CODEWITHSUNDEEP
sqlsqlite
Andrew
Andrew

Reputation: 513

SQL: Count instances of one attribute per another

I'm learning SQL and have set what seemed a simple task. I have a list of fictional people who have been given an intelligence rating from 1 (a bit dim) to 5 (Einstein watch out!), and whose gender is identified:

name  gender intelligence
Bob   Male   4
Jane  Female 5
Gary  Male   1
Linda Female 2
Stan  Male   2
Barb  Female 4
... list truncated...

What I would like to do is report, "for each level of intelligence, how many males and how many females are there? For the sample, I'm looking for something like this:

Intelligence    Male    Female
1               1       0
2               1       1
3               0       0
4               1       1
5               0       1

It's easy enough to come up with a list of each level of intelligence:

SELECT DISTINCT(intelligence) from table ORDER BY intelligence;

Now my general-purpose programming side says to construct a loop around that, but that both seems wrong and isn't (apparently) simple to do. Or, at least, not simple enough for my novice capabilities. Any suggestions on how to accomplish this? Is this genuinely harder than I think it is? I've learned about query basics - IN, LIKE, HAVING, CASE, nested SELECT, etc.

My more general goal is to be able to answer questions like, "For each type of X found in the table, how many of each type of Y are there?"

Upvotes: 0

Views: 440

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use conditional aggregation:

select intelligence, sum(gender = 'Male') as num_males, sum(gender = 'Female') as num_females
from t
group by intelligence;

SQLite treats boolean expressions as numbers, with "1" for true and "0" for false. In other databases, you would typically need an explicit case:

select intelligence,
       sum(case when gender = 'Male' then 1 else 0 end) as num_males,
       sum(case when gender = 'Female' then 1 else 0 end) as num_females
from t
group by intelligence;

Of course, this also works in SQLite, but it is more verbose.

Upvotes: 2

Related Questions