🚀 go-pugleaf

        Pardon me for sounding like a newbie here, but I am really stuck
        with trying to write a SQL statement (listed below).  I am trying to do
        this in C++ currently to go against a MSSQL Server so I am stuck (as far
        as I know with Transact). If someone has other suggestions as to another
        product that would give me additional ability with my SQL statements,
        I would be more than all ears..as it were.  Many TIAs for anyone that
        could help or provide guidance on this.

        ================

>> TABLE: totals

====================================
FIELDA       FIELDB       FIELDC
====================================
B             100          150
C             210          350
D             145           0
====================================

        from a table similar to the one listed below.  This selects the
value of Col1 and provides the sum of Col3 into either FieldB or FieldC
based on the value of Col2 (i.e. if Col2 is 1 then the Sum of Col3 into
fieldB...etc..)

>> Table: data
===============================
Col1      Col2        Col3
===============================
B           1          100
B           2          150
C           1          210
C           2          350
D           1          145
===============================

John Murray
jmurray@dnasunnet.dna.mci.com


[Respone]

   John,

   Given, the tables as you described. I would write the following
   SQL transaction.

       1. Insert Primary Key column into the Totals Table.
       2. Update the Aggregate Columns of the Totals Table.


        BEGIN TRANSACTION

                INSERT INTO totals ( FIELDA ) VALUES
                        SELECT DISTINCT col1 FROM data;

                UPDATE totals SET totals.fieldb =
                                ( SELECT SUM(col3) FROM data
                                  WHERE  col2 = 1
                                  AND    totals.fielda = data.col1
                                ),
                                    totals.fieldc =
                                ( SELECT SUM(col3) FROM data
                                  WHERE  col2 = 2
                                  AND    totals.fielda = data.col1
                                )
        END TRANSACTION;


        Note: You may need to write some additional SQL to handle cleaning
              up the total table before you run the above transaction.

        Other solutions...

        You could always write a Trigger that populates the total table
        for you as data is inserted into the data table, thus avoiding
        have the front-end application performing the aggregated SQL
        statement all together.


        Questions,

        1. Why not have the totals table be value based instead of having the
        columns denote 1 ( FLDB ) and 2 ( FLDC ), if you add new groupings
        to the data table, you'll have to modify the totals table. Performance?


        Totals: FLDA    ID              PK1
                FLDB    Groupd ID       PK2
                FLDC    Total

        2. I have not used C++, but SQL is pretty much independent of the
        host application language ( C, C++, Visual Basic.....).


        Joe Bangs
        jbangs@intellution.com