Select Sum with Group By

[Former Member]

Hi ABAPers,

For below code I am not getting sum values:

select   werks              " plant
           mcs_date           " mcs date ok
           shift_code         " shift ok
           mktype           " mk type
           matnr
*           attribute1         " Additional data
           sum( qty_ltr )     " qty_ltr ok
           sum( qty_kg )      " qty kg ok
           sum( ft_kg )      " ft kg ok
           sum( snf_kg )      " snf kg ok
           sum( net_price )   " base price / net price
           into corresponding fields of table i_zmp_pledger_smry12
           from zmp_pledger
           where werks in s_werks and
           mcs_date in s_date and
           shift_code in s_shift and
           mktype in s_mtype and
           matnr in ('BGM', 'CGM' )
*           attribute1 in s_charg
          group by werks mcs_date shift_code mktype matnr.
*           order by werks mcs_date shift_code mktype matnr ascending."attribute1.

kindly suggest changes.

--Dep

[Former Member]

Hi,

Instead of this ,you first select all records and the do SUM it will improvem your query performance also,

select werks " plant

mcs_date " mcs date ok

shift_code " shift ok

mktype " mk type

matnr

qty_ltr " qty_ltr ok

qty_kg " qty kg ok

ft_kg " ft kg ok

snf_kg " snf kg ok

net_price " base price / net price

into corresponding fields of table i_zmp_pledger_smry12

from zmp_pledger

where werks in s_werks and

mcs_date in s_date and

shift_code in s_shift and

mktype in s_mtype and

matnr in ('BGM', 'CGM' )

• attribute1 in s_charg

group by werks mcs_date shift_code mktype matnr.

• order by werks mcs_date shift_code mktype matnr ascending."attribute1.

Loop at i_zmp_pledger_smry12.

qty_ltr = i_zmp_pledger_smry12-qty_ltr + qty_ltr.

qty_kg = i_zmp_pledger_smry12-qty_kg + qty_kg .

ft_kg = i_zmp_pledger_smry12-ft_kg +ft_kg .

snf_kg = i_zmp_pledger_smry12-snf_kg + snf_kg.

net_price = i_zmp_pledger_smry12-net_price + net_price.

modify i_zmp_pledger_smry12.

clear i_zmp_pledger_smry12.

endloop.

Hope this will be useful,

Manjunath M

[Former Member]

Thank you Manju,

But I dont want ot do that way..

[awin_prabhu]

The data type of the column for which SUM is calculated should be Numerical.

Pls check its data type in your database table in SE11.

sum( qty_ltr ) " qty_ltr ok

sum( qty_kg ) " qty kg ok

sum( ft_kg ) " ft kg ok

sum( snf_kg ) " snf kg ok

sum( net_price ) " base price / net price

[Former Member]

Hi,

Data type is DEC length 12with decimals 2 for qty_ltr and qty_kg

QUAN length 15 decimals 3 for fat_kg and snf_kg and

CURR length 11decimals 2 for net_price.

[awin_prabhu]

Data matching all your WHERE conditions, might not be available.

Comment the WHERE conditons and check for availability of data in database table.

Or verfiy data availability by giving WHERE condition one by one.

[Former Member]

I think is in the GROUP BY the problem, in SQL The GROUP fields must be in the same order of the SELECT fields excluding the SUM fields.

Try not using INTO CORRESPONDING FIELDS, use a copy of the table "zmp_pledger" to put the SUM data.

hope this help.

Edited by: David Funez on Dec 27, 2010 6:13 PM

Edited by: David Funez on Dec 27, 2010 6:16 PM

[titus_thomas2]

<deleted and reposted>

[titus_thomas2]

Dear Deepak,

The problem with the following code is when you use INTO CORRESPONDING FIELDS OF, internally the db searches for the field name in the structure/internal table with the same name as specified in your select list. Since you select sum( qty_ltr ), the system tries to find a field in your internal table i_zmp_pledger_smry12 for a field named sum( qty_ltr ). Since it doesnt find any, the values are not stored into the internal table. To avoid this, there are 2 options.

1. The best option (as far as i know) would be to declare an internal typed structure with only those fields that you require in the internal table.

For eg.

TYPES: BEGIN OF st_zmp_pledger_smry12,
                werks     type zmp_pledger-werks ,           " plant
                mcs_date     type zmp_pledger-mcs_date ,      " mcs date ok
                shift_code   type zmp_pledger-shift_code  ,    " shift ok
                mktype         type zmp_pledger-mktype ,          " mk type
                matnr           type zmp_pledger-matnr ,
                qty_ltr          type zmp_pledger-qty_ltr ,           " qty_ltr ok
                qty_kg          type zmp_pledger-qty_kg  ,         " qty kg ok
                ft_kg             type zmp_pledger-ft_kg ,           " ft kg ok
                snf_kg          type zmp_pledger-snf_kg ,        " snf kg ok
                net_price      type zmp_pledger-net_price,
          END OF st_zmp_pledger_smry12.

DATA: it_zmp_pledger_smry12_2  TYPE STANDARD TABLE OF st_zmp_pledger_smry12.

Then, proceed with your select

select   werks              " plant
           mcs_date           " mcs date ok
           shift_code         " shift ok
           mktype           " mk type
           matnr
*           attribute1         " Additional data
           sum( qty_ltr )     " qty_ltr ok
           sum( qty_kg )      " qty kg ok
           sum( ft_kg )      " ft kg ok
           sum( snf_kg )      " snf kg ok
           sum( net_price )   " base price / net price
           into table it_zmp_pledger_smry12_2
           from zmp_pledger
           where werks in s_werks and
           mcs_date in s_date and
           shift_code in s_shift and
           mktype in s_mtype and
           matnr in ('BGM', 'CGM' )
*           attribute1 in s_charg
          group by werks mcs_date shift_code mktype matnr.

Now, you will be able to find the values in your internal table.

2. The second option is rather simpler. You can specify alias names for all your sum fields.

In this case, when you sum( qty_ltr ) as qty_ltr.

Your select stmt would look something like this,

select   werks              " plant
           mcs_date           " mcs date ok
           shift_code         " shift ok
           mktype           " mk type
           matnr
*           attribute1         " Additional data
           sum( qty_ltr ) as qty_ltr    " qty_ltr ok
           sum( qty_kg ) as qty_kg      " qty kg ok
           sum( ft_kg )  as ft_kg     " ft kg ok
           sum( snf_kg ) as snf_kg      " snf kg ok
           sum( net_price ) as net_price " base price / net price
           into corresponding fields of table i_zmp_pledger_smry12
           from zmp_pledger
           where werks in s_werks and
           mcs_date in s_date and
           shift_code in s_shift and
           mktype in s_mtype and
           matnr in ('BGM', 'CGM' )
*           attribute1 in s_charg
          group by werks mcs_date shift_code mktype matnr.

Hope this helps,

Titus

[Former Member]

Thank You David and Titus your suggestions are absolutely correct but David first

any ways guys Happy new year and take care..