asked
Subquery needs to add results to main query group by
How can I get my subquery to add its values to the main query grouping levels > Msg 116, Level 16, State 1, Line 15 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. SELECT Retailer AS [Retailer] ,Year AS [Year] ,Week_of_User AS [Week] ,Planogram AS [Store Grade] ,sum([packs sold]) AS [Last weeks sales units (packs)] --Sums up the sales by Store Grade for the current week -- I also need to sum up the year to date sales by Store Grade -- If I type this I get an aggregated sum, the same value for every Store Grade -- ,(SELECT sum([packs sold]) FROM SalesData WHERE Year = Year_of_User) AS [Sales units year to date (packs)] -- It seems reasonable to write this: **,(SELECT planogram, sum([packs sold]) FROM SalesData WHERE Year = Year_of_User GROUP BY Planogram) AS [Sales units year to date (packs)]** -- However I now get an error telling me to use EXISTS but I have been unable to work this out. -- How should I be doing this. FROM SalesData WHERE year = year_of_User AND week = week_of_user GROUP BY Retailer ,Year ,Week_of_User ,[Planogram] ,[Current number of stores in chain] ;
You are trying to use a correlated subsquery, but not providing enough for the subquery to 'correlate' with. Try : SELECT Retailer AS [Retailer] ,Year AS [Year] ,Week_of_User AS [Week] ,Planogram AS [Store Grade] ,sum([packs sold]) AS [Last weeks sales units (packs)] --Sums up the sales by Store Grade for the current week ,(SELECT sum([packs sold]) FROM SalesData sd 2 where Year = Year_of_User and sd2.Planogram = sd1.Planogram) AS [Sales units year to date (packs)] FROM SalesData sd1 WHERE year = year_of_User AND week = week_of_user GROUP BY Retailer ,Year ,Week_of_User ,[Planogram] ,[Current number of stores in chain] ; There will be a much better way of doing this, but lets see if this gives the right data first
Hi Kev many thanks, by my calculation (and supporting reports) that is spot on. I would be interested to know better ways. I am trying to work out the logic here: you have added sd1 and sd2 against SalesData inside and outside of the subquery so Planogram can be linked together...Is that what the missing piece of information was? Martin
Yes essentially you need to 'pass into' the subquery something that ties it in to the outer query. Take a look at
http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx However because the inner sub query is executed for every row in the outer query , the performance can suffer. Sometimes it is better to have a subquery that returns the data that you can then join on. Other times you may be able to change the subquery into a CROSS APPLY.
ohhhh that CROSS APPLY looks interesting. I'll let you know how I get on with it. Also thanks for hyperlink, half the time knowing the correct phrasing helps when using google. :o)
14 People are following this question.
