How can I get several counts of a column in one SQL query?

Question

I have a schema like:

PetOwner:
owner_id | first_name | last_name
---------------------------------
1        | john       | smith
2        | jane       | doe
3        | jack       | jones    

Pets:
pet_id | owner_id | name | type
-------------------------------
1      | 1        | a    | dog
2      | 1        | b    | dog
3      | 1        | c    | dog
4      | 1        | d    | cat
5      | 1        | e    | cat
6      | 1        | f    | fish
7      | 2        | g    | dog
8      | 2        | h    | dog
9      | 2        | i    | fish
10     | 3        | j    | dog

And I'd like to write a query that gives me:

first_name | last_name | dog_count | cat_count | fish_count
-----------------------------------------------------------
john       | smith     | 3         | 2         | 1
jane       | doe       | 2         | 0         | 1
jack       | jones     | 1         | 0         | 0

There is a known set of pet types, so this doesn't need to make a column for arbitrary types. In addition, any owners that don't have a certain type of pet should get a 0 count for that pet type.

I've already tried a query like:

SELECT first_name, last_name, owner_id, type, COUNT(*)
FROM Pets, PetsOwner
GROUP_BY owner_id, type

which yields:

first_name | last_name | owner_id | type | COUNT(*)
---------------------------------------------------
john       | smith     | 1        | dog  | 3
john       | smith     | 1        | cat  | 2
john       | smith     | 1        | fish | 1
jane       | doe       | 2        | dog  | 2
jane       | doe       | 2        | fish | 1
jack       | jones     | 3        | dog  | 1

But this requires iterating over the result set to get the output I'm looking for, obviously something to avoid with SQL. Also it doesn't provide the default 0 value for pet types that are absent from the table.

Is it possible to do this with one SQL query? If not, is there a query I could do that is better (i.e. easier to work with) than the query I've found?

It's a MySQL database, if that makes a difference.

Thanks for the help.

PinnyM · Accepted Answer · 2013-06-27 17:12:52Z

2

Use SUM instead of COUNT:

SELECT o.first_name, o.last_name, 
       SUM(CASE WHEN p.type = 'cat' THEN 1 ELSE 0 END) as cat_count,
       SUM(CASE WHEN p.type = 'dog' THEN 1 ELSE 0 END) as dog_count,
       SUM(CASE WHEN p.type = 'fish' THEN 1 ELSE 0 END) as fish_count
FROM PetOwners o
JOIN Pets p ON o.id = p.owner_id
GROUP BY o.id

And if you want to include owners that don't have any pets at all, change JOIN to LEFT JOIN.

answered Jun 27, 2013 at 17:12

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I get several counts of a column in one SQL query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related