mhutter mhutter - 1 year ago 89
SQL Question

select count by value

Given a table

messages
with the following fields:

id | Number
customer_id | Number
source | VARCHAR2
...


I want to know how many messages each customer has, but I want to differentiate between messages where
source
equals to
'xml'
and all other sources.

My query so far

SELECT customer_id,
case when source = 'xml' then 'xml' else 'manual' end as xml,
count(*)
FROM MESSAGES
GROUP BY customer_id,
case when source = 'xml' then 'xml' else 'manual' end;


which gives me a result similar to this:

customer_id | xml | count
----------------------------
1 | xml | 12
1 | manual | 34
2 | xml | 54
3 | xml | 77
3 | manual | 1
...


This is rather ugly in two ways:


  1. I have to repeat the
    case
    statement in both the field list and in the group list

  2. I now have two rows per customer.



Q: Is it possible to formulate a query, such that the result looks like this instead?

customer_id | xml | manual
--------------------------
1 | 12 | 34
2 | 54 | 0
3 | 11 | 1

vkp vkp
Answer Source

Use conditional aggregation.

SELECT customer_id,
  sum(case when source = 'xml' then 1 else 0 end) as xml,
  sum(case when source <> 'xml' then 1 else 0 end) as manual
FROM MESSAGES
GROUP BY customer_id

This assumes the source column is non null. If it can be null use coalesce or nvl in the case expression so the comparison gives you expected results.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download