multiple sums represented in different columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple sums represented in different columns

Author

Topic

Ziggadebo
Starting Member

20 Posts

Posted - 2011-04-18 : 09:04:30

I'm trying to breakdown some data held in table by using the sum function, I then want the next column to show a subset of the data returned by the first sum. This is much more easily explained by way of an example.

Here is my original table called 'whatanimals':

Animal Qty Owner
dog 2 jim
cat 1 bob
mouse 1 betty
hamster 2 bobby
dog 2 bill
dog 3 brian

Now my first query would be

select Animal,sum(Qty) from whatanimals
group by Animal

which gives

Animal Qty
dog 7
cat 1
mouse 1
hamster 2

What I then want to add to my results are extra columns, representing the number of animals by Owner

so i would have

Animal Qty Jim Bob
dog 7 2 0
cat 1 0 1
mouse 1 0 0
hamster 2 0 0
fish 0 0 0

and so on, I'm not sure the best way to construct this (or anyway if I'm honest) I've tried fiddling around with case statements but am getting knowhere fast, could someone please point me in the right direction?

Many Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 09:27:21

select Animal,sum(Qty), jim = SUM(case when owner = 'jim' then qty else 0 end), Bob = SUM(case when owner = 'Bob' then qty else 0 end)
from whatanimals
group by Animal

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ziggadebo
Starting Member

20 Posts

Posted - 2011-04-18 : 09:58:12

quote:
Originally posted by nigelrivett

select Animal,sum(Qty), jim = SUM(case when owner = 'jim' then qty else 0 end), Bob = SUM(case when owner = 'Bob' then qty else 0 end)
from whatanimals
group by Animal

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks very much, thats exactly what I've been trying to do. This really helps thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources