General
Fan

## 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...

`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;`