How to display Current & Previous Month Name along with Total of Current Revenue per month & Total o-CodePudding

I have the following tables:

[Revenue Raw Data] consisting of Month_ID,Revenue

and it has the following sample data:

yt_Calendar_lookup consisting of Month_ID,Month_No,Month_Name

and it has the following sample data:

Month_No
1
2
3
4
5
6
7
8
9
10
11
12

Month_ID
120
121
122
123
124
125
126
127
128
129
130
131

Month_Name
January
February
March
April
May
June
July
August
September
October
November
December

What I am trying to achieve is to display the results for the following 5 columns:

Display Current Month as the first column from the table yt_Calendar_lookup
Display Total Revenue for the Current Month as the second column from the table [Revenue Raw Data]
Display Previous Month as the third column from the table yt_Calendar_lookup
Display Total Revenue for the Previous Month as the fourth column from the table [Revenue Raw Data]
Display Revenue difference between Current Month and Previous Month as the fifth column from the table [Revenue Raw Data]

Initially, this is what I have tried:

SELECT
    A.[Total Revenue for Current Month]
    , B.[Total Revenue for Previous Month]
    , A.[Total Revenue for current Month] - B.[Total Revenue for previous Month] AS 'Revenue Difference between current Month and previous Month'
FROM (
    SELECT SUM(CAST(Revenue AS BIGINT)) AS 'Total Revenue for Current Month' 
    FROM [Revenue Raw Data]
    WHERE Month_ID IN (SELECT MAX(Month_ID) FROM [Revenue Raw Data])
) A, (
    SELECT SUM(CAST(Revenue AS BIGINT)) AS 'Total Revenue for Previous Month'
    FROM [Revenue Raw Data]
    WHERE Month_ID IN (SELECT MAX(Month_ID) - 1 FROM [Revenue Raw Data])
) B

With the query above, I could successfully display the following 3 columns:

Total Revenue for Current Month, Total Revenue for Previous Month, Revenue Difference between Current Month and Previous Month

And this is the result I have received:

Total Revenue for Current Month Total Revenue for Previous Month
21221564                        19973825

Revenue Difference between current Month and previous Month
1247739

With the next query, I was trying to display the following 5 columns:

Current Month, Total Revenue for Current Month, Previous Month, Total Revenue for Previous Month, Revenue Difference between Current Month and Previous Month

This is what I have tried:

    SELECT A.[Current Month], 
A.[Total Revenue for Current Month], 
    B.[Previous Month], 
B.[Total Revenue for Previous Month],
    A.[Total Revenue for current Month] - B.[Total Revenue for previous Month] 
    AS 'Revenue Difference between current Month and previous Month'
    FROM
    (SELECT Month_Name 
AS 'Current Month', 
    SUM(CAST(Revenue AS BIGINT)) 
    AS 'Total Revenue for Current Month' 
    FROM [Revenue Raw Data], yt_Calendar_lookup
    WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
    AND [Revenue Raw Data].Month_ID IN 
    (SELECT MAX(Month_ID) FROM [Revenue Raw Data])
    ) A,
    (SELECT Month_Name 
AS 'Previous Month', 
    SUM(CAST(Revenue AS BIGINT)) 
    AS 'Total Revenue for Previous Month'
    FROM [Revenue Raw Data], yt_Calendar_lookup
    WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
    AND [Revenue Raw Data].Month_ID IN 
(SELECT MAX(Month_ID) - 1 FROM [Revenue Raw Data])
    ) B

I received the following error message:

Column 'yt_Calendar_lookup.Month_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I even tried by adding GROUP BY A.Month_Name,B.Month_Name, and even after that I am receiving the same error message.

I am still trying to figure out on where I am making the mistake for this particular query.

Solution for this particular issue will be highly appreciated.

CodePudding user response：

You just had to add GROUP BY Month_Name to the 2 sub-queries.

SELECT 
  C.[Current Month], 
  C.[Total Revenue for Current Month], 
  P.[Previous Month], 
  P.[Total Revenue for Previous Month],
  C.[Total Revenue for current Month] - P.[Total Revenue for previous Month] 
    AS [Revenue Difference between current Month and previous Month] 
 FROM
 (
   SELECT Month_Name AS [Current Month], 
    SUM(CAST(Revenue AS BIGINT)) AS [Total Revenue for Current Month] 
   FROM [Revenue Raw Data], yt_Calendar_lookup
   WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
     AND [Revenue Raw Data].Month_ID IN (
           SELECT MAX(Month_ID) 
           FROM [Revenue Raw Data])
   GROUP BY Month_Name
) C
CROSS JOIN 
(
  SELECT Month_Name AS [Previous Month], 
   SUM(CAST(Revenue AS BIGINT)) AS [Total Revenue for Previous Month] 
  FROM [Revenue Raw Data], yt_Calendar_lookup
  WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
    AND [Revenue Raw Data].Month_ID IN (
          SELECT MAX(Month_ID) - 1 
          FROM [Revenue Raw Data])
  GROUP BY Month_Name
) P;

But this can also be calculated via conditional aggregation.

WITH CTE_MONTHS AS (
  SELECT TOP 2 
    Month_ID
  , ROW_NUMBER() OVER (ORDER BY Month_ID DESC) AS rn
  FROM [Revenue Raw Data]
  GROUP BY Month_ID
  ORDER BY Month_ID DESC
)
SELECT 
  Month1 AS [Current Month]
, Revenue1 AS [Total Revenue for Current Month] 
, Month2 AS [Previous Month] 
, Revenue2 AS [Total Revenue for Previous Month] 
, Revenue1 - Revenue2 AS [Revenue Difference between current Month and previous Month] 
FROM
(
SELECT 
  MAX(CASE WHEN m.rn = 1 THEN c.Month_Name END) AS Month1
, MAX(CASE WHEN m.rn = 2 THEN c.Month_Name END) AS Month2
, SUM(CASE WHEN m.rn = 1 THEN CAST(r.Revenue AS BIGINT) END) AS Revenue1
, SUM(CASE WHEN m.rn = 2 THEN CAST(r.Revenue AS BIGINT) END) AS Revenue2
FROM [Revenue Raw Data] r
JOIN CTE_MONTHS m ON m.Month_ID = r.Month_ID
LEFT JOIN yt_Calendar_lookup c ON c.Month_ID = r.Month_ID
) q;

CodePudding user response：

I finally managed to solve the problem. As mentioned by one of the StackOverflow user, that all I had to add was GROUP BY Month_Name on both the sub-queries.

This is what I have done:

SELECT A.[Current Month], A.[Total Revenue for Current Month], B.[Previous Month], B.[Total Revenue for Previous Month],
A.[Total Revenue for current Month] - B.[Total Revenue for previous Month] AS 'Revenue Difference between current Month and previous Month'
FROM
(SELECT Month_Name AS 'Current Month', SUM(CAST(Revenue AS BIGINT)) AS 'Total Revenue for Current Month' 
FROM [Revenue Raw Data], yt_Calendar_lookup
WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
AND [Revenue Raw Data].Month_ID IN (SELECT MAX(Month_ID) FROM [Revenue Raw Data])
GROUP BY Month_Name
) A,
(SELECT Month_Name AS 'Previous Month', SUM(CAST(Revenue AS BIGINT)) AS 'Total Revenue for Previous Month'
FROM [Revenue Raw Data], yt_Calendar_lookup
WHERE [Revenue Raw Data].Month_ID = yt_Calendar_lookup.Month_ID
AND [Revenue Raw Data].Month_ID IN (SELECT MAX(Month_ID) - 1 FROM [Revenue Raw Data])
GROUP BY Month_Name
) B

And it works perfectly.