CASE Statement VS Multiple Left Joins

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

SQL Server 2012 Forums

Transact-SQL (2012)

CASE Statement VS Multiple Left Joins

Author

Topic

winny86
Starting Member

7 Posts

Posted - 2013-06-13 : 06:44:48

Hello,

I was producing a query that basically output the total amount of money made over a year per media device (playstation, ipad etc).

I then produce a similar query but the total amount of money made per month in the year:

SELECT
SUM(ft.TransactionTotal) AS 'Total Amount Spent'
,COALESCE(a.iPadTotal,0) AS 'iPad Total'
,COALESCE(b.MacintoshTotal,0) AS 'Mac Total'
,COALESCE(c.AndroidTotal,0) AS 'Android Total'
,COALESCE(d.WindowsNTTotal,0) AS 'Windows NT Total'
,COALESCE(e.SamsungGeneric,0) AS 'Samsung Total'
FROM Tables
LEFT JOIN (
SELECT mem.MemberID, FirstName, LastName, SUM(ft.TransactionTotal) AS iPadTotal
FROM TABLES
WHERE DeviceClassName = 'APPLE IPAD'
GROUP BY mem.MemberID, FirstName, LastName ) a ON a.MemberId = mem.MemberId
LEFT JOIN (
SELECT mem.MemberID, FirstName, LastName, SUM(ft.TransactionTotal) AS MacintoshTotal
FROM TABLES
WHERE DeviceClassName = 'APPLE MACINTOSH'
GROUP BY mem.MemberID, FirstName, LastName ) b ON b.MemberId = mem.MemberId
LEFT JOIN (
SELECT mem.MemberID, FirstName, LastName, SUM(ft.TransactionTotal) AS AndroidTotal
FROM TABLES
WHERE DeviceClassName = 'ANDROID TABLET'
GROUP BY mem.MemberID, FirstName, LastName ) c ON c.MemberId = mem.MemberId
LEFT JOIN (
SELECT mem.MemberID, FirstName, LastName, SUM(ft.TransactionTotal) AS WindowsNTTotal
FROM TABLES
WHERE DeviceClassName = 'WINDOWS NT PC'
GROUP BY mem.MemberID, FirstName, LastName ) d ON d.MemberId = mem.MemberId
LEFT JOIN (
SELECT mem.MemberID, FirstName, LastName, SUM(ft.TransactionTotal) AS SamsungGeneric
FROM TABLES
WHERE DeviceClassName = 'SAMSUNG GENERIC 2010-11'
GROUP BY mem.MemberID, FirstName, LastName ) e ON e.MemberId = mem.MemberId

GROUP BY a.iPadTotal, b.MacintoshTotal, c.AndroidTotal, d.WindowsNTTotal, e.SamsungGeneric
ORDER BY SUM(ft.TransactionTotal) DESC

I then did the same query in the following way:

SELECT
SUM(CASE WHEN dc.Name = 'APPLE IPAD' THEN Amount ELSE 0 END) AS 'Apple iPad'
, SUM(CASE WHEN dc.Name = 'APPLE MACINTOSH' THEN Amount ELSE 0 END) AS 'Mac'
, SUM(CASE WHEN dc.Name = 'ANDROID TABLET' THEN Amount ELSE 0 END) AS 'Tablet'
, SUM(CASE WHEN dc.Name = 'WINDOWS NT PC' THEN Amount ELSE 0 END) AS 'Windows NT'
, SUM(CASE WHEN dc.Name = 'SAMSUNG GENERIC 2010-11' THEN Amount ELSE 0 END) AS 'Samsung'
, SUM(Amount) AS 'Total Amount'
FROM TABLES

Can any body advise on whether one way is better than the other and why?

Thanks,

Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 06:55:56

it would be latter as former requires reading and joining from same table multiple times

you can check execution plan as well as timings for both.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources