SQL group by in Subquery-CodePudding

I'm trying to get monthly production using group by after converting the unix column into regular timestamp. Can you please tell how to use group by here in the code.

'''

With production(SystemId, dayof, monthof, yearof, powerwatts, productionwattshours) as 
(
Select   SystemId,
            [dayof] = DAY(hrdtc), 
            [monthof] = MONTH(hrdtc),
            [yearof] = YEAR(hrdtc),
        powerwatts, productionwatthours
    from (
    Select  * , dateadd(s, UnixTime, '19700101') as hrdtc from enphasemeterreading ) ds
    )

    Select * from production
    where systemId = 2368252

'''

CodePudding user response：

I think you're looking for this (technically you don't need a subquery but it allows you to avoid repeating the DATEADD() expression):

SELECT SystemId = 2368252,
  [Month] = DATEFROMPARTS(YEAR(hrdtc), MONTH(hrdtc), 1),
  powerwatts = SUM(powerwatts),
  productionwatthours = SUM(productionwatthours)
FROM
(
  SELECT powerwatts, productionwatthours,
    DATEADD(SECOND, UnixTime, '19700101') as hrdtc
  FROM dbo.enphasemeterreading
  WHERE systemId = 2368252 
) AS ds 
GROUP BY DATEFROMPARTS(YEAR(hrdtc), MONTH(hrdtc), 1);

If you want to also avoid repeating the GROUP BY expression:

SELECT SystemId = 2368252,
  [Month],
  powerwatts = SUM(powerwatts),
  productionwatthours = SUM(productionwatthours)
FROM
(
  SELECT [Month] = DATEFROMPARTS(YEAR(hrdtc), MONTH(hrdtc), 1),
    powerwatts, productionwatthours
  FROM
  (
    SELECT powerwatts, productionwatthours,
      DATEADD(SECOND, UnixTime, '19700101') as hrdtc
    FROM dbo.enphasemeterreading
    WHERE systemId = 2368252 
  ) AS ds1
) AS ds2 
GROUP BY [Month];

Personally I don't think that's any prettier or clearer. A couple of other tips:

Spell it out; shorthand is lazy and problematic
Always qualify tables and other objects with schema

Updated requirement (please state these up front): How would I join this query to another table?

SELECT * FROM dbo.SomeOtherTable AS sot
INNER JOIN
(
  SELECT SystemId = 2368252,
    [Month],
    powerwatts = SUM(powerwatts),
    productionwatthours = SUM(productionwatthours)
  FROM
  ...
  GROUP BY [Month]
) AS agg
ON sot.SystemId = agg.SystemId;