sql grouping of calculation

Question

Does anyone have a solution how to group sums of two categories with case statements?

In the below screenshot i would like to merge two rows to one and just display PE and NPE sums in the columns flag1total and flag2total, but it's not working.

This is the SQL I have at the moment:

select type, flag, coalesce(sum(value),0) total,
case when flag = "NPE" then coalesce(sum(value),0) else "" end as flag1total,
case when flag = "PE" then coalesce(sum(value),0) else "" end as flag2total
from maintable
group by type, flag;

The data looks like this

 type     flag          value
 P_OTH    PE            23525
 P_OTH    PE            13525
 P_CRE    PE            232525
 P_OTH    PE            4525
 ....

The expected result is this (one line per type and vectorized sums for the flags):

 type     flag          total      flag1total   flag2total
 P_OTH    PE, NPE       3023624    1132707      1890917

Update your question and add also the expcted result (and data sample) as tabular text ..(not image) — ScaisEdge, Jan 22 '20 at 14:44
Sorry this is unclear to me. You want the total for PE, total for NPE and the sum of the total NPE and total PE ? — Cyril Rebreyend, Jan 22 '20 at 14:50
updated the questions... i just want flag1total and flag2total... the general sum is there just to illustrate — dfhdfh, Jan 22 '20 at 14:53
I think you'll find happiness there : https://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql — Cyril Rebreyend, Jan 22 '20 at 14:55

score 1 · Accepted Answer · answered Jan 22 '20 at 14:57

1

You need to use case inside your sum function. Try something like this:

select type, coalesce(sum(value),0) total,
    coalesce(sum(case when flag = "NPE" then value else 0 end),0)   as flag1total,
    coalesce(sum(case when flag = "PE" then value else 0 end),0)  as flag2total
 from maintable
 group by type;

Note that 'flag' can't be in group by clause.

answered Jan 22 '20 at 14:57

Gabriel Cândido

56
4

2

just to be sure, this only applies when grouping, otherwise i can use my old query using case outside of my function? – dfhdfh Jan 22 '20 at 15:27
1

@GabrielCandido . . . The `coalesce()` is unnecessary. – Gordon Linoff Jan 22 '20 at 16:11
@dfhdfh yes, it's not wrong to use it outside, depends on how do you want the result to be. – Gabriel Cândido Jan 22 '20 at 16:40
@GordonLinoff true, didn't think through it. thanks for pointing out. – Gabriel Cândido Jan 22 '20 at 16:42

score 1 · Answer 2 · answered Jan 22 '20 at 14:59

Do you need below -

SELECT  type
       ,GROUP_CONCAT(flag, ', ') flag
       ,coalesce(sum(value),0) total
       ,coalesce(sum(case when flag = "NPE" then value else 0 end),0)   as flag1total
       ,coalesce(sum(case when flag = "PE" then value else 0 end),0)  as flag2total
 from maintable
 group by type;

sql grouping of calculation

2 Answers2