Using sums and counts with different criteria

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

Using sums and counts with different criteria

Author

Topic

rogerg07
Starting Member

2 Posts

Posted - 2010-08-21 : 08:55:44

Hello there,

I am quite new to sequel, and have a query that I would like to be able to run, but am having a little trouble with and couldn't seem to find a similar question. Basicall I have 2 tables, similar to the following but simplified a bit:

PersonID Name
--------------------
1 Roger
2 David
3 Pete
4 Bill

Then another table with purchases, linked by the PersonID
PurchaseID PersonID Category Amount
------------------------------------------------------
1 1 Books 10
2 1 DVDs 15
3 3 Books 10
4 4 Books 10
5 1 Books 5
6 2 DVDs 5
7 3 Books 10
8 2 Books 10
9 4 DVDs 5
10 1 DVDs 10

So this table just lists a purchase made by each person, whether it was a book or a DVD, and the amount they paid. Now what I want to do is to run a query that will

list all the people in the Person table, and sum up how many and how much they spent on books, and how many and how much they spend on DVDs, such as follows. I'll just

use the first line as an example, which will be Roger with PersonID1.

Person BookAmt BookSpend DVDAmt DVDSpend
----------------------------------------------------------------
Roger 2 15 2 25

The trouble I am having is figuring out how to use two different SUM() and count() functions with different criteria, ie. one which is HAVING Category = "Books", and another which is HAVING Category = "DVDs" while summing up the amount column.

Any help would be greatly appreciated, and thank you in advance for helping me on my way in to the world of SQL!

Roger

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-21 : 11:00:10

Try this:


SELECT (SELECT Per.Name
          FROM Personnel AS Per
         WHERE Per.PersonID = Pur.PersonID) AS Person,
       SUM(CASE WHEN Category = 'Books' THEN 1 ELSE 0 END) AS BookAmt,
       SUM(CASE WHEN Category = 'Books' THEN Amount ELSE 0 END) AS BookSpend,
       SUM(CASE WHEN Category = 'DVDs' THEN 1 ELSE 0 END) AS DVDAmt,
       SUM(CASE WHEN Category = 'DVDs' THEN Amount ELSE 0 END) AS DVDSpend
  FROM purchases AS Pur
 GROUP BY PersonID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:13:24

you can use join rather than subquery to get person details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rogerg07
Starting Member

2 Posts

Posted - 2010-08-23 : 16:22:16

Thanks very much guys, that seems to have done the trick!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:20:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources