SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 CASE Statement VS Multiple Left Joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

winny86
Starting Member

7 Posts

Posted - 06/13/2013 :  06:44:48  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/13/2013 :  06:55:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000