Column count based on a condition

Question

I have created an oracle query like as shown below,the query is working fine but the problem is that I want one more column which is the count of name where category should be A and id should be 1

SELECT name, velocity, COUNT(*) AS count, category FROM section GROUP BY name, velocity

Can anyone please tell me some solution for this

Possible duplicate of [Conditional Count on a field](http://stackoverflow.com/questions/1288058/conditional-count-on-a-field) — C8H10N4O2, Apr 06 '17 at 15:21

score 41 · Accepted Answer · answered Oct 16 '15 at 12:08

41

SELECT name, velocity, COUNT(*) AS count, 
COUNT(CASE WHEN category = 'A' AND id = 1 THEN 1 END)
FROM section 
GROUP BY name, velocity

This should work.

If record does not meet the condition then it will return a NULL, and count skips NULL fields.

answered Oct 16 '15 at 12:08

Canburak Tümer

951
20
35

Tatiana · Answer 2 · 2015-10-16T11:55:39.793

3

Something like this:

SELECT name, velocity, COUNT(*) AS count, 
SUM(CASE WHEN category = 'A' AND id = 1 THEN 1 ELSE 0 END)
FROM section 
GROUP BY name, velocity

edited Oct 16 '15 at 11:55

answered Oct 16 '15 at 11:49

Tatiana

1,489
8
18

You forgot the id condition – Mihai Oct 16 '15 at 11:49

score 1 · Answer 3 · answered Oct 16 '15 at 11:48

1

SELECT name, velocity, COUNT(*) AS count, category, 
       (select count(distinct name) from section where category = 'A' and id = 1)
FROM section 
GROUP BY name, velocity

answered Oct 16 '15 at 11:48

juergen d

195,137
36
275
343

Column count based on a condition

3 Answers3