Group by and sub total

[ss_kiran]

I need to populate temporary table from a database table. The records of the table are somthing like this
Country Type Amount
------------------------------
Ind FNT 10
Ind FNT 9
Ind FGH 1
Ind Sales 50
Ind FGH 5
USA FGH 4
USA FNT 3
USA Sales 100
USA FNT 1
USA FGH 39

I want to creat a Temp table which would have one record for every county and type. for ex from above records i should have

Ind FNT 19
Ind FGH 6
Ind Sales 50

USA FNT 4
USA FGH 43
USA Sales 100

Thanks

Kiran Shankar

 

[joey.jeremiah]

/**
 * group by temp-table 
 */

define temp-table ttGroup no-undo

    field tCountry  as char
    field tCategory as char
    field tTotal    as deci

    index tCntryCat is primary unique
          tCountry
          tCategory.



run fillGroup.



/**
 * fill group by temp-table 
 */

procedure fillGroup:

    /* clear previous data */

    empty temp-table ttGroup.



    /**
     * loop through my_table 
     * and break into country, category groups 
     */

    for each my_table no-lock

        break
        by my_table.country
        by my_table.category:



        /* accumulate amount for country, category group */

        accumulate my_table.amount ( total by my_table.category ).



        /**
         * when a country, category group ends
         * create a record in the group temp-table 
         * with the total amount for the group
         */

        if last-of( my_table.category ) then do:

            create ttGroup.
            assign
                ttGroup.tCountry    = my_table.country
                ttGroup.tCategory   = my_table.category
                ttGroup.tTotal      =

                    ( accum total by my_table.category mt_table.amount ).

        end. /* last-of( category ) */

    end. /* each my_table */

end procedure. /* fillGroup */

hth

 

[ss_kiran]

Thanks Joey!!

 

[jpachut]

Dear SS Kiran

I am also trying to do a subtotal by a specific field name and I was wondering if the suggestion you received worked or if you found another way to get your groub by and subtotal ?

JP

 

[ss_kiran]

Hello JP,

Yes the solution provided above by Joey did help me. I was able to achieve what i wanted.

Thanks
Kiran