How to get a sum of a sum in the query? Please help!

Question

Post reply

How to get a sum of a sum in the query? Please help!

jenniferhu46

SSCrazy

Points: 2990
More actions
August 10, 2006 at 2:27 pm

#173671

Hi experts,

I have the following query to return Daily comp sales, Weekly comp sales, and Period comp sales data for each store in a certain district.   This query works fine.

Now, the report owner request to add a returning result : The total for the district.

For example: The original result
Location_2 Location_1 Daily       WTD         PTD
09486       D 1069         2148.05      4706.32      16257.52
01217       D 1069           2211.45      5211.45      16918.52
05983       D 1069         2173.71      4962.62      16834.89
01397       D 1069          2183.59      5182.63      17301.14

The new result should be like

Location_2 Location_1 Daily       WTD         PTD
D 1069      D 1069     8716.8     20063.02     67312.07
09486       D 1069         2148.05      4706.32      16257.52
01217       D 1069           2211.45      5211.45      16918.52
05983       D 1069         2173.71      4962.62      16834.89
01397       D 1069          2183.59      5182.63      17301.14

How to change the following query to achieve that??? PLEASE HELP!

SELECT D.Location_2,D.Location_1,
         CASE WHEN D.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((D.TY - D.PY) / D.PY)*100) END Daily,
         CASE WHEN W.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((W.TY - W.PY) / W.PY)*100) END WTD,
         CASE WHEN P.PY = 0 THEN NULL ELSE Convert (Decimal(9,2),((P.TY - P.PY) / P.PY)*100) END PTD
         FROM          ( SELECT   Location_1,Location_2,
                      SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
                     SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
                     FROM micros.Daily_Summary_table GROUP BY Location_1,Location_2
                  ) D INNER JOIN
                  ( SELECT   Location_1,Location_2,
                     SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
                     SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
                     FROM micros.Weekly_Report_Table GROUP BY Location_1,Location_2
                  ) W on D.Location_2 = W.Location_2 INNER JOIN
                  ( SELECT   Location_1,Location_2,
                     SUM(CASE WHEN Measure_Name = 'A CompSales' THEN Measure_Value ELSE 0 END) TY,
                     SUM(CASE WHEN Measure_Name = 'PY WTD Net Sales' THEN Measure_Value ELSE 0 END) PY
                     FROM micros.PTD_Summary_Tble GROUP BY Location_1, Location_2
                  ) P on D.Location_2 = P.Location_2
         where D.Location_1= (SELECT Align_lvl_Long FROM micros.Mgmt_Personnel_Table
                             WHERE fullname=’allen.m’))

Viewing 2 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323581 More actions · Answer 1

you want to use a sub select in this case, if you want the summary data from the select statement you made;

something like this should give you the idea:

SELECT Location_1 as Location_2,Location_1,SUM(Daily) as Daily,sum(WTD) as WTD,SUM(PTD) as PTD

from ([big sql you posted goes here tween the parenthesis] ) GROUP BY Location_1

UNION

[big sql you posted goes here ]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!