I have this query and I’m not able to group by month

Question

DECLARE @Year INT = 2005, @Month INT = 7 
SELECT   --SequenceNo = ROW_NUMBER() OVER(ORDER BY OrderDate),
         Year = Year(convert(int,OrderDate,111)),
         case Month(convert(int,OrderDate,111)) 
        when 1 then 'Janeiro'
        when 2 then 'Fevereiro'
        when 3 then 'Março'
        when 4 then 'Abril'
        when 5 then 'Maio'
        when 6 then 'Junho'
        when 7 then 'Julho'  
        when 8 then 'Agosto'
        when 9 then 'Setembro'
        when 10 then 'Outubro'
        when 11 then 'Novembro'
        when 12 then 'Dezembro' 
        else 'unknown'
end as "Month1",
         Month = Month(convert(int,OrderDate,111)),
         TotalDue = convert(money,TotalDue,1),
         [Running Total] = convert(money,
         (SELECT sum(convert(money,TotalDue,1))
          FROM   Sales.SalesOrderHeader as Header
          WHERE  SalesOrderID <= soh.SalesOrderID
               AND year(OrderDate) = @Year
               AND month(OrderDate) = @Month),
                                         1)
FROM     Sales.SalesOrderHeader soh
WHERE    year(OrderDate) = @Year
         AND month(OrderDate) = @Month
group by Year, Month
order by 1, 2

When I try to run the group by appears:

Error Msg 207, Level 16, State 1, Line 31 Invalid column name 'Year'. Msg 207, Level 16, State 1, Line 31 Invalid column name 'Month'.

Answer 1

You didn’t put the columns year and month in the SELECT. To use the clause GROUP BY it is necessary that the columns that will be aggregated are in the SELECT.

Answer 2

The order for the execution of a query is the following:

1. clause FROM
2. clause WHERE
3. clause GROUP BY
4. clause HAVING
5. clause SELECT
6. clause ORDER BY

So you can’t use one GROUP BY with a column named. Here is a possible change in the query for you to get the desired result:

DECLARE @Year  INT = 2005,
        @Month INT = 7;

WITH principal (Year, Month, TotalDue, RunningTotal) AS (
  SELECT DATEPART(YEAR, CONVERT(INT, OrderDate, 111)) AS Year,
         DATEPART(MONTH, CONVERT(INT, OrderDate, 111)) AS Month,
         CONVERT(MONEY, TotalDue, 1) AS TotalDue,
         CONVERT(MONEY, (SELECT SUM(CONVERT(MONEY, TotalDue, 1))
                           FROM Sales.SalesOrderHeader AS Header
                          WHERE SalesOrderID <= soh.SalesOrderID
                            AND DATEPART(YEAR, OrderDate) = @Year
                            AND DATEPART(MONTH, OrderDate) = @Month), 1) AS RunningTotal
    FROM Sales.SalesOrderHeader soh
   WHERE DATEPART(YEAR, OrderDate) = @Year
     AND DATEPART(MONTH, OrderDate) = @Month
)
SELECT  p.Year,
        p.Month,
        CASE Month
          WHEN 1 THEN 'Janeiro'
          WHEN 2 THEN 'Fevereiro'
          WHEN 3 THEN 'Março'
          WHEN 4 THEN 'Abril'
          WHEN 5 THEN 'Maio'
          WHEN 6 THEN 'Junho'
          WHEN 7 THEN 'Julho'
          WHEN 8 THEN 'Agosto'
          WHEN 9 THEN 'Setembro'
          WHEN 10 THEN 'Outubro'
          WHEN 11 THEN 'Novembro'
          WHEN 12 THEN 'Dezembro'
          ELSE 'unknown'
        END AS 'Month1',
        SUM(p.TotalDue) AS 'TotalDue',
        SUM(p.RunningTotal) AS 'Running Total'
  FROM principal p
 GROUP BY Year,
          Month
 ORDER BY Year,
          Month;