Can we use coalesce and aggregate function in one derived columns

General

Can we use coalesce and aggregate function in one derived columns

Hi All,

My scenario here is I need to apply some logic to a table. consider there are 4 table a,b,c and we have 5 columns in each like a1,a2,a3,a4,a5 similarly for table b (b1,b2,b3,b4,b5,b6) and table c(c1,c2,c3,c4,c5).

I need to apply the below logic to get the appropriate value:

INSERT INTO sample(
c1,
c2,
c3,
b4,
coalesce(main.a5,0) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0)
WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0)
ELSE 0 END) AS data
from b left join c on (b.b3 = c.c3)
left join a on (c.c3 = a.a3)
group by 1,2,3,4,5;

When i execute the same i get error on group by saying 'group by clause may not contain aggregate column(sum)'

If i remove group by like 

INSERT INTO sample(
c1,
c2,
c3,
b4,
coalesce(main.a5,0) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0)
WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0)
ELSE 0 END) AS data
from b left join c on (b.b3 = c.c3)
left join a on (c.c3 = a.a3)
group by 1,2,3,4;

This also gives error like 'non aggregated column (coalesce) must be a part of group by clause.

Since both the things happens in same column its contrary. I know this wont work. Can any one suggest a better way for this...


1 REPLY
Junior Contributor

Re: Can we use coalesce and aggregate function in one derived columns

I can't follow your logic, so I don't know if this is correct, but adding an aggregate function to the coalesce(main.a5,0) or removing the SUM should at least remove that error:

INSERT INTO sample(
c1,
c2,
c3,
b4,
coalesce(main.a5,0) + (CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0)
WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0)
ELSE 0 END) AS data
from b left join c on (b.b3 = c.c3)
left join a on (c.c3 = a.a3)
group by 1,2,3,4,5;

INSERT INTO sample(
c1,
c2,
c3,
b4,
MIN(coalesce(main.a5,0)) + SUM(CASE WHEN b.b6 = '+' THEN COALESCE(b.b5,0)
WHEN b.b6 = '-' THEN -1*COALESCE(b.b5,0)
ELSE 0 END) AS data
from b left join c on (b.b3 = c.c3)
left join a on (c.c3 = a.a3)
group by 1,2,3,4;