asked on

sql server 2008 - Derived Table SUM (Aggregate)

Hello Experts,
Can you please look at this and give me a solution. I don't want to change this format of the derived table as I have tons created same way but want to aggregate result of both sums like this
SUM ( A1.CM_Sales + B1.YTD_Sales) AS 'Total Sales' -> I want to sum result of both the derived tables

SELECT a1.Sto_Name,
CM_Count,
CM_Sales,
YTD_Count,
YTD_Sales
FROM (SELECT b.Sto_Name,
COUNT(*) AS CM_Count,
SUM(Sal_Amt) AS CM_Sales
FROM Sales a
JOIN Stores b ON a.Sto_ID = b.Sto_ID
WHERE DATEPART(yy,Sal_Date) = DATEPART(yy,@RunDate) AND
DATEPART(mm,Sal_Date) = (DATEPART(mm,@RunDate)-1)
GROUP BY b.Sto_Name) AS A1

JOIN (SELECT Sto_Name,
COUNT(*) AS YTD_Count,
SUM(Sal_Amt) AS YTD_Sales
FROM Sales a
JOIN Stores b ON a.Sto_ID = b.Sto_ID
WHERE DATEPART(yy,Sal_Date) = DATEPART(yy,@RunDate)
GROUP BY b.Sto_Name) AS B1 ON a1.Sto_Name = b1.Sto_Name

PortletPaul

Are you sure you want to add the Year To Date with Current Month? (normally YTD already includes CM)

That could double count the current month.

Are you certain DATEPART(mm,Sal_Date) = (DATEPART(mm,@RunDate)-1) will always work correctly?
(what happens if @RunDate is in Januarury?)

And, are you sure you don't want to simplify, e.g.

SELECT
      Sto_Name
    , COUNT(CASE WHEN DATEPART(mm, Sal_Date) = (DATEPART(mm, @RunDate) - 1) THEN a.Sto_ID_ID END)  AS CM_Count
    , SUM(CASE WHEN DATEPART(mm, Sal_Date) = (DATEPART(mm, @RunDate) - 1) THEN Sal_Amt ELSE 0 END) AS CM_Sales
    , COUNT(*)                                                                                     AS YTD_Count
    , SUM(Sal_Amt)                                                                                 AS YTD_Sales
FROM Sales a
      JOIN Stores b
                  ON a.Sto_ID = b.Sto_ID
WHERE DATEPART(yy, Sal_Date) = DATEPART(yy, @RunDate)
GROUP BY
      b.Sto_Name
;

Open in new window

BI 0824

ASKER

The one given is not mine I mean the query but all the queries I need to deal with r the same like example given. So I just need to know how to sum both the values in the given format

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BI 0824

ASKER

Sorry about the late reply. It worked for me the way you did. Thanks.