CODEWITHSUNDEEP
sqlcountgroup-by
LucasSeveryn
LucasSeveryn

Reputation: 6302

Counting occurrences of value in SQL

Lets say we have a table nobel(yr, subject, winner).

I want to get a result which will return year, column of number of chemistry awards in that year, column of number of physics awards in that year.

How would you do that?

SELECT yr, count(subject='Physics'), count(subject='Chemistry') FROM nobel GROUP BY yr

does not work.

Upvotes: 1

Views: 119

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Won't this work?

select yr, subject, count(*) awards
from nobel
where subject in ('physics', 'chemistry')
group by yr, subject

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Your query does not work because the conditional returns a value of 0 or 1, and count counts non-NULL values.

Try using sum instead of count:

SELECT yr, sum(subject='Physics'), sum(subject='Chemistry')
FROM nobel
GROUP BY yr

By the way, not all databases treat conditional expressions as integers. The standard syntax would be:

select yr, sum(case when subject = 'Physics' then 1 else 0 end) as NumPhysics,
       sum(case when subject = 'Chemistry' then 1 else 0 end) as NumChemistry
from nobel
group by yr

You can also get the same information on multiple rows by doing:

select yr, subject, count(*)
from Nobel
where subject in ('Physics', 'Chemistry')
group by yr, subject

Upvotes: 4

Related Questions