mhutter mhutter - 3 months ago 12
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

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.

Comments