topic SQL calculation in Application Development and Automation Discussions

SQL calculation

Former Member — Wed, 16 May 2007 09:17:20 GMT

Can i write a sql that will sum up all quantity with debit/credit indicator. For example

In table of mseg, i need to sum up the MENGE which related to specific material number, but i have to care about SHKZG, i can't use to the following SQL

select sum( MENGE ) FROM mseg group by matnr.

Can i write a simple sql which will sum up MENGE with credit/debit indicator. Thanks

Re: SQL calculation

Former Member — Wed, 16 May 2007 09:20:27 GMT

Hi ,

Don't write select query . Use <b>SUM</b> command

select menge from mseg into itab.

SUM.

Regards,

Hemant

Re: SQL calculation

Former Member — Wed, 16 May 2007 09:22:56 GMT

hI,

No, you cannot write sql like this at max u can write:


select sum(menge) from mseg group by matnr shkzg.  "This will reduce resultset records.


or simply do :

loop at itab.

if itab-shkzg = <+>.
toatal_menge = total_menge + itab-menge.
else.
toatal_menge = total_menge - itab-menge.

endif.

Jogdand M B

Re: SQL calculation

Former Member — Wed, 16 May 2007 09:28:28 GMT

There is no SQL to help you sum one field according to the values of another field.

You need to select the data into an internal table and sum.

SELECT menge shkzg INTO itab.

DATA: sum type menge.

LOOP at itab.

CASE itab-shkzg.

WHEN 'H'.

sum = sum + itab-menge.

WHEN 'S'.

sum = sum - itab-menge.

ENDCASE.

ENDLOOP.

The + and - will vary according to situation.

Hope this helps