CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
CodeNinja
CodeNinja

Reputation: 3278

Group a query by every month

I have the following query : 

select

(select Sum(Stores) from XYZ where Year = '2013' and Month = '8' )
-
(select Sum(SalesStores) from ABC where Year = '2013' and Month = '8') as difference

Here in the above query Year and Month are also columns of a table.

I would like to know if there is a way to run the same query so that , it is run against every month of the year ?

Upvotes: 1

Views: 381

Answers (5)

user30410
user30410

Reputation: 131

  ;WITH Months(Month) AS
    (
        SELECT 1 
        UNION ALL
        SELECT Month + 1
        FROM Months
        where Month < 12
    )

    SELECT Months. Month ,
(select isnull(Sum(Stores),0) from XYZ where Year = '2013' and Month = Months.Month) - (select isnull(Sum(SalesStores),0) from ABC where Year = '2013' and Month =Months.Month) as difference
    FROM Months

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

In the following example uses the UNION ALL operator with CTE

;WITH cte AS
 (SELECT SUM(Stores) AS Stores, [Month]
  FROM dbo.XYZ
  WHERE [Year] = '2013'
  GROUP BY [Month]      
  UNION ALL
  SELECT -1.00 * SUM(SalesStores), [Month]
  FROM dbo.ABC      
  WHERE [Year] = '2013'
  GROUP BY [Month]
  )
  SELECT [Month], SUM(Stores) AS Difference
  FROM cte  
  GROUP BY [Month]

Demo on SQLFiddle

Upvotes: 0

Jack Jiang
Jack Jiang

Reputation: 522

;WITH Months(Month) AS
(
    SELECT 1 
    UNION ALL
    SELECT Month + 1
    FROM Months
    where Month < 12
)


SELECT '2013' [Year], m.Month, COALESCE(SUM(Stores), 0) - COALESCE(SUM(SalesStores), 0) [Difference]
FROM months m
LEFT JOIN XYZ x ON m.Month = x.Month
LEFT JOIN ABC a ON a.Month = m.Month

GROUP BY m.Month

Upvotes: 2

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

If there are months without data/rows within XYZ or ABC tables then I would use FULL OUTER JOIN:

SELECT ISNULL(x.[Month], y.[Month]) AS [Month],
       ISNULL(x.Sum_Stores, 0) - ISNULL(y.Sum_SalesStores, 0) AS Difference
FROM   
(
    SELECT [Month], Sum(Stores) AS Sum_Stores 
    FROM   XYZ 
    WHERE  [Year] = '2013' 
    GROUP BY [Month]
) AS x
FULL OUTER JOIN
(
    SELECT [Month], Sum(SalesStores) AS Sum_SalesStores 
    FROM   ABC 
    WHERE  [Year] = '2013' 
    GROUP BY [Month]
) AS y ON x.[Month] = y.[Month]

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726669

You could use GROUP BY in your inner trades, and then run a join, like this:

SELECT left.Month, (left.sum - COALESCE(right.sum, 0)) as difference
FROM (
    SELECT Month, SUM(Stores) as sum
    FROM XYZ WHERE Year = '2013'
    GROUP BY Month
) left
LEFT OUTER JOIN (
    SELECT Month, SUM(Stores) as sum
    FROM ABC WHERE Year = '2013'
    GROUP BY Month
) right ON left.Month = right.Months

Note the use of COALESCE. It lets you preserve the value of the first SUM in case when there are no records for the month in the ABC table.

Upvotes: 1

Related Questions