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

Question

My SQL query runs perfectly, but when I add the CTE function, I get an error Please check this code and let me know what's wrong with the CTE:

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

Answer 1

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;