GROUP By problem

[jimmie]

Hi,

I have a small problem with a query. I know I can solve this by using subquerys but I want to do it without that so please give me some guidance.

I have a table, lets call it animals. In this table there are id,owner,type.
Type can be 0,1,2 -> cat,dog, bird.

What i want to do is create a query that returns this set:

owner, # of cats, #of dogs, #of birds

that means:
owner, count for type 0,count for type 1,count for type 2 on the SAME row.

This is easy to do with subquerys but where talking about a database with millions of rows and this query is just one of many nightly querys so I need it to be as effective as possible. And of course the table is a bit more complex with related tables involved in the query and it's going to be grouped by 2 more parameters but I want to keep it simple for you guys.

I'm looking for a function like group_concat but that puts the data into different fields and not the same... That would be the perfect solution.

[guelphdad]

what you need then is to use the case statement:


select ownername,
sum(case when type='cat' then 1 else 0 end) as cats,
sum(case when type='dog' then 1 else 0 end) as dogs,
sum(case when type='bird' then 1 else 0 end) as birds
from yourtablename
group by ownername


If you want just the one total for all of those as well then you can do:

select ownername,
sum(case when type='cat' then 1 else 0 end) as cats,
sum(case when type='dog' then 1 else 0 end) as dogs,
sum(case when type='bird' then 1 else 0 end) as birds,
sum(case when type='cat' then 1 else 0 end) +
sum(case when type='dog' then 1 else 0 end) +
sum(case when type='bird' then 1 else 0 end) as total
from yourtablename
group by ownername

edit: forgot the group by clause earlier. it's there now.

[Philipp]

i would do it like guelphdad