asked on

sql case sum and group

I need to find a way of doing this

Database is Oracle 9i (9.2.0)

I have the two tables below

where SOI_ITTYPE = R i need to make the value negative and sum SOI_INVVAL returning one line per CMP_GROUP from the second table, see examples below i think they will mae sense,

if i take SOI_ITTYPE from the group by clause i get an error

WHEN SOI_ITTYPE = 'R' THEN SUM(SOI_INVVAL * -1) ELSE
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

Thanks

Ian

Table 1 - SOITEM
SOI_ORDREF | SOI_INVVAL | SOI_PRODUCT  | SOI_ITTYPE
S0000001      |            100.00| ABC123             | O
S0000002      |              25.00| ABC144             | R
S0000003      |              30.00| ABC141             | O
 
Table 2 - CMPPROD
 
CMP_PRODUCT | CMP_GROUP |
ABC123             | TG
ABC140             | SD
ABC144             | TG
ABC141             | TG
 
 
 
SELECT CMP_GROUP, 
(CASE
	  WHEN SOI_ITTYPE = 'R' THEN SUM(SOI_INVVAL * -1) ELSE
	   SUM(SOI_INVVAL)
    END) TOTAL
FROM SOITEM LEFT JOIN CMPROD ON SOITEM.SOI_PRODUCT=CMPROD.CMP_PRODUCT
WHERE CMP_GROUP='TG'
GROUP BY CMP_GROUP, SOI_ITTYPE;
 
 
CMP_      TOTAL
---- ----------
TG    130.00
TG     -25,00
 
 
Desired result 
 
CMP_      TOTAL
---- ----------
TG    105.00

Open in new window

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

You can try with DECODE also in place of CASE statement.

SELECT CMP_GROUP, 
	SUM(DECODE(SOI_ITTYPE,'R',-1,1) * SOI_INVVAL) TOTAL
FROM SOITEM 
LEFT JOIN CMPROD ON SOITEM.SOI_PRODUCT=CMPROD.CMP_PRODUCT
WHERE CMP_GROUP='TG'
GROUP BY CMP_GROUP;

Open in new window

IanPaskin

ASKER

Yes that works perfect