I have this query and I am not able to group by month

Question

I have this query and I am not able to group by month

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

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 group by it 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'.

sql-server query ssms

asked by anonymous 10.08.2017 / 23:19

2 answers

How to use REST API from SharePoint Online via local HTML Notifications with SignalR

score 1 · Answer 1

1

You did not put the columns year and month into SELECT . To use the GROUP BY clause, the columns to be aggregated must be in SELECT .

10.08.2017 / 23:42

score 0 · Answer 2

The order of execution of a query is as follows:

clause FROM

clause WHERE

clause GROUP BY

clause HAVING

clause SELECT

clause ORDER BY

So you can not use a GROUP BY with a named column. Here is a possible change in query so that you 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;