Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

use of group by and sum in sap

Former Member
0 Kudos

Hi all ,

I m writing a program in which i have to fetch material from table mbewh with its total stock at any yrs period = 12 .code is as follow

tables : mbewh ,
          mbew .


data : begin of itab occurs 100 ,
        matnr like mbewh-matnr ,
        bwkey like mbewh-bwkey ,
        bwtar like mbewh-bwtar ,
        lfgja like mbewh-lfgja ,
        lfmon like mbewh-lfmon ,
        lbkum LIKE mbewh-lbkum ,
        total like mbewh-lbkum ,
        end of itab .

data : begin of itab1 occurs 100 ,
         matnr like mbewh-matnr ,
         lbkum like mbewh-lbkum ,
         end of itab1 .

DATA : RETURNCODE LIKE SY-SUBRC ,
         field(40) type c .

data BEGIN OF IHEADER OCCURS 10  ,
        field_name(25TYPE C,
         END OF IHEADER.

refresh iheader .
clear iheader .

IHEADER-field_name  = 'Material number'.
append iheader.
IHEADER-field_name  = 'Plant'.
append iheader.
IHEADER-field_name  = 'valuation '.
append iheader.
IHEADER-field_name  = 'Year '.
append iheader.
"IHEADER-field_name  = 'plant'.
"append iheader.
IHEADER-field_name  = 'Month'.
append iheader.
IHEADER-field_name  = 'Stock'.
append iheader.
IHEADER-field_name  = 'Total_stock'.
append iheader.


select-options : matnr for mbewh-matnr ,
                  bwkey for mbewh-bwkey ,
                  bwtar for mbewh-bwtar .

parameters : lfgja like mbewh-lfgja OBLIGATORY.

refresh itab .
clear itab .
refresh itab .
clear itab .

select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum )
    INTO (itab-matnr , itab-bwkey , itab-bwtar , itab-lfgja , itab-lfmon , itab-lbkum , itab-total )
   FROM mbewh
     WHERE matnr in matnr AND
         bwkey in bwkey AND
         lfgja = lfgja and
         lfmon = '12'
   group by (matnr)
    .
     append itab .
   endselect .

sort itab .


   CALL FUNCTION 'HR_DISPLAY_BASIC_LIST'
             EXPORTING
                BASIC_LIST_TITLE = SY-TITLE              "JRFPH0K009969
                FILE_NAME        = SPACE
*              HEAD_LINE1       = SY-TITLE              "JRFPH0K009969
*              HEAD_LINE1       = 'Employee Promotion Report'
             " HEAD_LINE2       = page_head
*              CURRENT_REPORT   = SY-REPID

             IMPORTING

               RETURN_CODE      = RETURNCODE

            TABLES
                  DATA_TAB         = itab
                  FIELDNAME_TAB    = iheader
*               SELECT_TAB       = LIST_TAB_OUT

             EXCEPTIONS

                  DOWNLOAD_PROBLEM = 1
                  OTHERS           = 2 .

                  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
                    write : ' No Data Is Present  Or You Havenot Entered The Period ' .
     ENDIF.

It is not giving me any compile time error when i m activating prog but when i try to run it gave me runtime errror

error : dbif_rsql_sql_error

exception : cx_sy_open_sql_db .

line which prompt error is :
select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum )

i need to find the total stock of all material (ie sum of stock of material in all plant )

please help me with it

.

Thanks in advance ,

shikha


10 REPLIES 10

former_member183224
Participant
0 Kudos

Hi Sikha,

As during select statement you are applying SUM keyword so only you are getting the run time error.

Better you can do like this: -

1. Get all your materials and its stocks for the specific period into one internal table.

2. Use control breaks statement as below:

               Loop At itab into wa.

                    At Last.

                        SUM.

" here move your stocks to another field which will be in the final table.                        

               Endloop.    

I hope it will work out.

Regards,

Gourav

0 Kudos

Your SELECT statement is not correct, selected fields must either be part of the GROUP option or aggregate functions.

So replace

select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum ) group by (matnr).

with a correct statement like

select matnr sum( lbkum ) group by (matnr).

or

select matnr bwkey bwtar lfgja lfmon sum( lbkum ) group by (matnr bwkey bwtar lfgja lfmon).


Regards,

Raymond

0 Kudos

Dear Raymond,

You are saying my select statement is not correct? Or the previous one?

former_member183224
Participant
0 Kudos

Dear Sikha,

Please find my code as below:

*&---------------------------------------------------------------------*

*& Report  ZGJ_TEST

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  zgj_test.

TABLES : mbewh ,

           mbew .

TYPES : BEGIN OF itab  ,

         matnr LIKE mbewh-matnr ,

         bwkey LIKE mbewh-bwkey ,

         bwtar LIKE mbewh-bwtar ,

         lfgja LIKE mbewh-lfgja ,

         lfmon LIKE mbewh-lfmon ,

         lbkum LIKE mbewh-lbkum ,

         END OF itab .

DATA: it TYPE TABLE OF itab,

       wa TYPE itab.

DATA: it_final TYPE TABLE OF itab,

       wa_final TYPE itab.

*data : begin of itab1 occurs 100 ,

*         matnr like mbewh-matnr ,

*         lbkum like mbewh-lbkum ,

*         end of itab1 .

DATA : returncode LIKE sy-subrc ,

          field(40) TYPE c .

DATA BEGIN OF iheader OCCURS 10  ,

         field_name(25TYPE c,

          END OF iheader.

REFRESH iheader .

CLEAR iheader .

iheader-field_name  = 'Material number'.

APPEND iheader.

iheader-field_name  = 'Plant'.

APPEND iheader.

iheader-field_name  = 'valuation '.

APPEND iheader.

iheader-field_name  = 'Year '.

APPEND iheader.

"IHEADER-field_name  = 'plant'.

"append iheader.

iheader-field_name  = 'Month'.

APPEND iheader.

iheader-field_name  = 'Stock'.

APPEND iheader.

iheader-field_name  = 'Total_stock'.

APPEND iheader.

SELECT-OPTIONS : matnr FOR mbewh-matnr ,

                   bwkey FOR mbewh-bwkey ,

                   bwtar FOR mbewh-bwtar .

PARAMETERS : lfgja LIKE mbewh-lfgja OBLIGATORY.

SELECT matnr bwkey bwtar lfgja lfmon lbkum INTO TABLE it

   FROM mbewh WHERE matnr IN matnr AND

          bwkey IN bwkey AND

          lfgja = lfgja AND

          lfmon = '12'.

DATA: lv_total TYPE mbewh-lbkum.

SORT it BY matnr.

LOOP AT it INTO wa.

   wa_final-bwkey = wa-bwkey.

   wa_final-bwtar = wa-bwtar.

   wa_final-lfgja = wa-lfgja.

   wa_final-lfmon = wa-lfmon.

   wa_final-matnr = wa-matnr.

   wa_final-lbkum = wa-lbkum.

   APPEND wa_final TO it_final.

   CLEAR: wa_final, wa.

ENDLOOP.

LOOP AT it_final INTO wa_final.

   AT END OF matnr.

     SUM.

     wa_final-lbkum = wa_final-lbkum.

   ENDAT.

ENDLOOP.

CALL FUNCTION 'HR_DISPLAY_BASIC_LIST'

   EXPORTING

     basic_list_title = sy-title              "JRFPH0K009969

     file_name        = space

*   HEAD_LINE1       = SY-TITLE              "JRFPH0K009969

*   HEAD_LINE1       = 'Employee Promotion Report'

                " HEAD_LINE2       = page_head

*   CURRENT_REPORT   = SY-REPID

   IMPORTING

     return_code      = returncode

   TABLES

     data_tab         = it_final

     fieldname_tab    = iheader

*   SELECT_TAB       = LIST_TAB_OUT

   EXCEPTIONS

     download_problem = 1

     OTHERS           = 2.

IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

   WRITE : ' No Data Is Present  Or You Havenot Entered The Period ' .

ENDIF.

0 Kudos

Dear Shikha,

I agree with in your case i think select should be written in below way:-

select matnr sum( lbkum )

    INTO (itab-matnr , itab-total )

   FROM mbewh

   WHERE matnr in matnr AND

         bwkey in bwkey AND

         lfgja = lfgja and

         lfmon = '12'

   group by (matnr)

    .

     append itab .

   endselect .

but Select Endselect should be avoided instead you can select all and use control break statements.

selecting all data in a internal table.

select matnr bwkey bwtar lfgja lfmon lbkum into table itab

   from mbewh where matnr in matnr and

          bwkey in bwkey and

          lfgja = lfgja and

          lfmon = '12'


loop at itab into wa_tab.

at end of matnr.

sum.

append wa_tab to itab2.

endat.

endloop.

Hope it helps you.

Thanks & Regards,

Vignesh Yeram

0 Kudos

hi gorav ,

Ur report donot give desire result .

beside wat is purpose of this stmt .

    wa_final-lbkum = wa_final-lbkum.

Thanks for the code

Regards ,

Shikha .

0 Kudos

This message was moderated.

0 Kudos

This message was moderated.

0 Kudos

This message was moderated.

0 Kudos

Dear Shikha,

I agree with Raymond Giuseppi in your case i think select should be written in below way:-

select matnr sum( lbkum )

    INTO (itab-matnr , itab-total )

   FROM mbewh

   WHERE matnr in matnr AND

         bwkey in bwkey AND

         lfgja = lfgja and

         lfmon = '12'

   group by (matnr)

    .

     append itab .

   endselect .

but Select Endselect should be avoided instead you can select all and use control break statements.

selecting all data in a internal table.

select matnr bwkey bwtar lfgja lfmon lbkum into table itab

   from mbewh where matnr in matnr and

          bwkey in bwkey and

          lfgja = lfgja and

          lfmon = '12'


loop at itab into wa_tab.

at end of matnr.

sum.

append wa_tab to itab2.

endat.

endloop.

Hope it helps you.

Thanks & Regards,

Vignesh Yeram