• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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
0
BI 0824
Asked:
BI 0824
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
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

0
 
BI 0824Author Commented:
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
0
 
PortletPaulCommented:
Whether they are yours or not isn't the issue - if you add those 2 numbers together the result will be wrong

The existing query will not work in January either.

Are you really determined to get a query that gives you bad results?

Here is how you would add those 2 numbers together:
All you need to do is place the calculation into the select clause, but you don't need SUM()

SELECT a1.Sto_Name,
       CM_Count,
       CM_Sales,
       YTD_Count,
       YTD_Sales,
       A1.CM_Sales + B1.YTD_Sales AS 'Total Sales' --<< BUT THIS IS A BAD NUMBER
FROM (
0
 
BI 0824Author Commented:
Sorry about the late reply. It worked for me the way you did. Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now