Merge repetitve queries into a single one

Question

Post reply

Merge repetitve queries into a single one

Nelson Casanova

SSCommitted

Points: 1570
More actions
February 3, 2016 at 6:17 am

#307686

Hi,
I'm looking at a query that adds up amounts per year and in some other cases per month but its basically the same query repeting itself once per operation/total. I was wondering what would be a best approach to it (?), heres one of them (they all look sort of the same):
SELECT
*
FROM
(SELECT SUM(EmployeePaid)*2 AS Employee2006 FROM TblRecords WHERE Year = 2006) t0,
(SELECT SUM(EmployeePaid)*2 AS Employee2007 FROM TblRecords WHERE Year = 2007) t1,
(SELECT SUM(EmployeePaid)*2 AS Employee2008 FROM TblRecords WHERE Year = 2008) t2,
(SELECT SUM(EmployeePaid)*2 AS Employee2009 FROM TblRecords WHERE Year = 2009) t3,
(SELECT SUM(EmployeePaid)*2 AS Employee2010 FROM TblRecords WHERE Year = 2010) t4,
(SELECT SUM(EmployeePaid)*2 AS Employee2011 FROM TblRecords WHERE Year = 2011) t5,
(SELECT SUM(EmployeePaid)*2 AS Employee2012 FROM TblRecords WHERE Year = 2012) t6,
(SELECT SUM(EmployeePaid)*2 AS Employee2013 FROM TblRecords WHERE Year = 2013) t7,
(SELECT SUM(EmployeePaid)*2 AS Employee2014 FROM TblRecords WHERE Year = 2014) t8,
(SELECT SUM(EmployeePaid)*2 AS Employee2015 FROM TblRecords WHERE Year = 2015) t9
Any help is appretiated
My stack: VS2015 ~ C# ~ MSSQL 2014[/url] ~ DevCraft ~ ReSharper ~ DataGrip ~ Linqer[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Wow.

If they can handle the output in a slightly different format, then you can change it to:

SELECT [Year], SUM(EmployeePaid) * 2

FROM TblRecords

GROUP BY [Year];

If the output has to remain in the same format, then it'll be slightly more complex:

SELECT SUM(CASE WHEN [Year] = 2006 THEN EmployeePaid ELSE 0 END) * 2 AS t0,

SUM(CASE WHEN [Year] = 2007 THEN EmployeePaid ELSE 0 END) * 2 AS t1,

(...)

FROM TblRecords;

(Both queries are untested)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT

Employee2006 = SUM(CASE WHEN [Year] = 2006 THEN EmployeePaid*2 ELSE 0 END),

Employee2015 = SUM(CASE WHEN [Year] = 2015 THEN EmployeePaid*2 ELSE 0 END)

FROM TblRecords

WHERE [Year] BETWEEN 2006 AND 2015

-- or

SELECT

Employee2006 = MAX(CASE WHEN [Year] = 2006 THEN SUMEmployeePaid ELSE 0 END),

Employee2015 = MAX(CASE WHEN [Year] = 2015 THEN SUMEmployeePaid ELSE 0 END)

FROM (

SELECT [Year], SUMEmployeePaid = SUM(EmployeePaid)*2

FROM TblRecords

WHERE [Year] BETWEEN 2006 AND 2015

GROUP BY [Year]

) d

Hugo's fast today - but the solutions are more or less the same.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nelson Casanova SSCommitted Points: 1570 More actions · Answer 3

Thanks for your replies... I'm thinking, its also sort of like the same, isnt it? I mean, you'll need to repeat each year as a new line... But next year someone would need to add a new line to the query so it works... I have a frontend doing row-by-row approach but I would like to delegate the whole thing to the sql server... thats mainly my goal... I tried a few cursor-like queries but no results at all...

My stack: VS2015 ~ C# ~ MSSQL 2014[/url] ~ DevCraft ~ ReSharper ~ DataGrip ~ Linqer[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

huasoBruto (2/3/2016)
Thanks for your replies... I'm thinking, its also sort of like the same, isnt it? I mean, you'll need to repeat each year as a new line... But next year someone would need to add a new line to the query so it works... I have a frontend doing row-by-row approach but I would like to delegate the whole thing to the sql server... thats mainly my goal... I tried a few cursor-like queries but no results at all...

It isn't the same at all. The table is read only once.

To avoid having to write out the years manually, use a dynamic cross-tab query[/url]. Post back if you have trouble working it out. You shouldn't, Jeff's articles are excellent.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nelson Casanova SSCommitted Points: 1570 More actions · Answer 5

I'll try to come up with a solution based on this approach but I find that the simple GROUP BY query would do, what I'll work on the frontend is to grab both columns (Year, Amount) and then simply put in 2 single comma separated strings that later would be use to generate graphs... Still a lot better that a dozen queries.

Thanks!

My stack: VS2015 ~ C# ~ MSSQL 2014[/url] ~ DevCraft ~ ReSharper ~ DataGrip ~ Linqer[/url]