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

IanPaskinAsked:
Who is Participating?
 
reb73Commented:
Try the revised code below -
SELECT CMP_GROUP, 
	SUM(CASE WHEN SOI_ITTYPE = 'R' THEN -1 ELSE 1 END * 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

0
 
SharathData EngineerCommented:
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

0
 
IanPaskinAuthor Commented:
Yes that works perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.