calculate row count as multiple column in postgresql

Question

I have table like below

+---------+-----------+---------+-------------+--------+
| call_id | seg_order | src_dn  | dst_dn_type | dst_dn |
+---------+-----------+---------+-------------+--------+
|   29192 |         1 | "8004"  |           0 | "2215" |
|   29193 |         1 | "8000"  |           6 | "2239" |
|   29194 |         1 | "10001" |           6 | "8802" |
|   29194 |         2 | "10001" |           6 | "8802" |
|   29194 |         3 | "10001" |           4 | "8003" |
|   29194 |         4 | "10001" |           4 | "8003" |
|   29194 |         5 | "8003"  |           0 | "2225" |
|   29194 |         6 | "8003"  |           0 | "2225" |
|   29194 |         7 | "10001" |           0 | "2225" |
|   29195 |         1 | "10000" |           6 | "8857" |
|   29195 |         2 | "10000" |           6 | "8857" |
|   29195 |         3 | "10000" |           4 | "8002" |
|   29195 |         4 | "10000" |           4 | "8002" |
|   29195 |         5 | "8002"  |           0 | "2213" |
|   29195 |         6 | "8002"  |           0 | "2213" |
|   29195 |         7 | "10000" |           0 | "2213" |
|   29196 |         1 | "10002" |           6 | "8800" |
|   29196 |         2 | "10002" |           6 | "8800" |
|   29196 |         3 | "10002" |           4 | "8000" |
|   29196 |         4 | "10002" |           4 | "8000" |
|   29196 |         5 | "8000"  |           0 | "2240" |
|   29196 |         6 | "8000"  |           0 | "2240" |
|   29196 |         7 | "10002" |           0 | "2240" |
|   29197 |         1 | "10003" |           6 | "8804" |
|   29198 |         1 | "8000"  |           0 | "2240" |
|   29199 |         1 | "8004"  |           0 | "2220" |
|   29200 |         1 | "8004"  |           0 | "2213" |
|   29201 |         1 | "10002" |           6 | "8800" |
|   29202 |         1 | "10003" |           6 | "8804" |
|   29202 |         2 | "10003" |           6 | "8804" |
|   29202 |         3 | "10003" |           2 | "8010" |
|   29202 |         4 | "10003" |           4 | "8004" |
|   29202 |         5 | "10003" |           4 | "8004" |
|   29202 |         6 | "8004"  |           0 | "2215" |
|   29202 |         7 | "8004"  |           0 | "2215" |
|   29202 |         8 | "10003" |           0 | "2215" |
+---------+-----------+---------+-------------+--------+

as a result, I need 3 column that contains;

how many "call_id" group has "10001" in "src_dn" column
how many "call_id" group has "10001" in "dst_dn" column
how many "call_id" group has "6" in "dst_dn_type" when "seg_order" is max (on last segment)

for first and second, "10001" should be count one per group.

Thanks

edit: expected result

+---------+-----------+---------+
| inbound | outbound  | ivr     |
+---------+-----------+---------+
|      1  |        0  |      1  |
+---------+-----------+---------+

edit: what I've done so far

this query works (I believe) for first and second steps

select * from
(select count(distinct call_id) from tbl where src_dn='10001') as t1,
(select count(distinct call_id) from tbl where dst_dn='10001') as t2

Can you also post expected output for the sample data of your post? — Giorgos Betsos
– Giorgos Betsos, Commented Mar 7, 2016 at 9:21
Please show what have you tried so far. We will help you fix any mistakes you make. — Ihor Romanchenko
– Ihor Romanchenko, Commented Mar 7, 2016 at 9:28

Roland · Accepted Answer · 2016-03-07 11:10:50Z

1

I think this is what you want:

SELECT
    COALESCE((SELECT count(DISTINCT call_id) FROM tbl WHERE src_dn = '10001'), 0) AS inbound,
    COALESCE((SELECT count(DISTINCT call_id) FROM tbl WHERE dst_dn = '10001'), 0) AS outbound,
    COALESCE((SELECT count(DISTINCT call_id) FROM tbl WHERE dst_dn_type = 6 
        AND seg_order = (select max(seg_order) from tbl)), 0) AS outbound;

The COALESCE funtion is to return 0. Here you can find how it works: http://www.postgresql.org/docs/9.3/static/functions-conditional.html

edited Mar 7, 2016 at 11:10

answered Mar 7, 2016 at 10:45

Roland

4875 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

James De Souza Over a year ago

I get "ERROR: more than one row returned by a subquery used as an expression" Btw, all calls does not have same segment count

Roland Over a year ago

See my edit. The problem was the group by I used. It returns single counts per call_id, not over all call_ids. Does it work for you?

James De Souza Over a year ago

many thanks Roland. I will mark your post as solution. update your query with this AND seg_order = (select max(seg_order) from tbl as t2 where t2.call_id=tbl.call_id group by call_id)

Collectives™ on Stack Overflow

calculate row count as multiple column in postgresql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related