Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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

0
IanPaskin
Asked:
IanPaskin
1 Solution
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now