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

  • 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’))

           

  • 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!

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

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