CODEWITHSUNDEEP
sqloracle-database
mhutter
mhutter

Reputation: 2916

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

Upvotes: 0

Views: 69

Answers (5)

Ashish Patil
Ashish Patil

Reputation: 4624

There is other way by using decode function apart from CASE:

 SELECT cust_id,
  COUNT(DECODE(source,'xml','xml'))"XML",
  COUNT(DECODE(source,'manual','manual'))"manual"
FROM MESSAGES
GROUP BY cust_id;

But, this won't show result when you have null as source.

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

This will work, it doesn't appear you have a source called 'manual'. COUNT or SUM will give you the same difference.

SELECT
    customer_id
    ,ISNULL(COUNT(CASE WHEN source = 'xml' THEN 1 END),0) xml
    ,ISNULL(COUNT(CASE WHEN source <> 'xml' OR source IS NULL THEN 1 END),0) manual
FROM Messages
GROUP BY customer_id

This will allow for zero to appear where you usually would see a NULL value, your sample has a zero rather than a null.

Upvotes: 1

user5683823
user5683823

Reputation:

Here is a fancy solution (it does almost exactly what vkp's solution does), using the PIVOT operation introduced in Oracle 11.1. Note how the distinction between 'xml' and all others (including NULL) is dealt with in the subquery.

select *
from (select customer_id, case when source = 'xml' then 'xml' else 'other' as source
      from messages)
pivot (count(*) for source in ('xml' as xml, 'other' as other))
;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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.

Upvotes: 3

user330315
user330315

Reputation:

You are looking for conditional aggregation:

SELECT customer_id,
       count(case when source = 'xml' then 1 end) as xml_count, 
       count(case when source <> 'xm' then 1 end) as manual_count
FROM MESSAGES
GROUP BY customer_id

This works because aggregates ignore NULL values and the result of the CASE will be NULL if source does not contain the value from the case condition.

Upvotes: 4

Related Questions