My SQL query runs perfectly, but when I add the CTE function, I get an error-CodePudding

Please check this code and let me know what's wrong with the CTE:

WITH Consumption_details(SonnenUnitId, consumption, monthof, yearof) AS
(
    SELECT 
        SonnenUnitId, SUM(apartment_consumption) AS consumption, 
        monthof, yearof  
    FROM 
        (SELECT 
             SonnenUnitId, apartment_consumption, 
             DATEPART(MONTH, day) AS monthof, 
             DATEPART(YEAR,day) AS yearof 
         FROM 
             SonnenMeterReading) AS t
    GROUP BY 
        yearof, monthof, SonnenUnitId
    HAVING 
        monthof = 2 AND yearof = 2022
    ORDER BY
        SonnenUnitID
)

CodePudding user response：

You can't have ORDER BY inside the CTE (unless you also include TOP, which you shouldn't do in this case), and you need to do something with the CTE - it's just an expression, not a query on its own.

;;;/*be safe!*/;;;With cd(SonnenUnitId, consumption, monthof, yearof) AS
    (
        SELECT SonnenUnitId, ...
        ...
        GROUP BY yearof, monthof, SonnenUnitId
        HAVING monthof =2 and yearof =2022
    )
SELECT * FROM cd Order by SonnenUnitID;

As an aside, this query could be a whole lot more efficient with no need for a CTE and a subquery, any of the HAVING, and the scan potentially becoming a seek.

DECLARE @mo int = 2, @yo int = 2022;


DECLARE @m date = DATEFROMPARTS(@yo, @mo, 1);

SELECT SonnenUnitId, 
  SUM(apartment_consumption) AS consumption, 
  monthof = @mo, 
  yearof = @yo  
FROM dbo.SonnenMeterReading
  WHERE [day] >= @m 
    AND [day] < DATEADD(MONTH, 1, @m)
  GROUP BY SonnenUnitId
  ORDER BY SonnenUnitId;