COUNT from two seperate tables

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

COUNT from two seperate tables

Author

Topic

jmarkee
Starting Member

9 Posts

Posted - 2014-04-29 : 15:42:57

I have two tables

Books
-BookID
-CustomerID

Magazines
-MagazineID
-CustomerID

How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:

CustomerID, BookCount, MagazineCount

Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-29 : 15:51:23

one way to write the query is this:

SELECT 
	CustomerId, 
	SUM(CASE WHEN Source = 'Books' THEN ItemCount ELSE 0 END) AS Books,
	SUM(CASE WHEN Source = 'Magazines' THEN ItemCount ELSE 0 END) AS Magazines
FROM
(
	SELECT CustomerID, COUNT(*) AS ItemCount, 'Books' AS Source FROM Tabl11
	UNION ALL
	SELECT CustomerID, COUNT(*), 'Magazines' AS Source FROM Table2
) s
GROUP BY
	CustomerId;

jmarkee
Starting Member

9 Posts

Posted - 2014-04-29 : 22:03:28

Thanks James K. This worked great. I have run across this a few times and was above my head. Learned something today, thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-30 : 11:56:02

Just for sake education, I tend to do this every so slightly differently:

SELECT 
	CustomerId, 
	SUM(BookCount) AS Books,
	SUM(MagazineCount) AS Magazines
FROM
(
	SELECT CustomerID, COUNT(*) AS BookCount, NULL AS MagazineCount FROM Tabl11
	UNION ALL
	SELECT CustomerID,  NULL AS BookCount, COUNT(*) AS MagazineCount FROM Table2
) s
GROUP BY
	CustomerId;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources