CODEWITHSUNDEEP
sqlinformix
Vodnik
Vodnik

Reputation: 129

How to modify a SQL SELECT request with GROUP BY

I'm using the following SQL request to Informix DB:

select fromQ, toQ, count(callid) as cont_num, type
from some_table
group by fromQ, toQ, type
order by fromQ, toQ;

It produces the result:

fromq   toq        cont_num  type
-----------------------------------
        Sales      12        1
        English    1         1
        MB         59        1
        Reception  3         2
        Reception  53        1
        Service    1         1
MB      Sales      1         1
MB      English    1         1

This is OK, as expected. Please note there are 2 rows for toq=Reception. Field WRTYPE can have values only from 1 to 3. So idea is to make an output like this:

fromq   toq        cont_num  type1  type2  type3
------------------------------------------------
        Sales      12        12     0      0
        English    1         1      0      0
        MB         59        59     0      0
        Reception  56        53     3      0
        Service    1         1      0      0
MB      Sales      1         1      0      0
MB      English    1         1      0      0

Is there a simple way to do this?

Upvotes: 2

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Use conditional aggregation:

select fromQ, toQ, count(callid) as cont_num,
       sum(case when type = 1 then 1 else 0 end) as type_1,
       sum(case when type = 2 then 1 else 0 end) as type_2,
       sum(case when type = 3 then 1 else 0 end) as type_3
from some_table
group by fromQ, toQ
order by fromQ, toQ;

Upvotes: 4

Related Questions