How to do sum with where clause group by with date?

I want to sum up where clause. I don’t know how to show off_duration in the next column.

SELECT CAST([RecordDateTime] AS DATE) AS DATE
    ,SUM(CAST([units] AS FLOAT)) AS Units
    ,sum(cast(duration AS INT) / 60) on_duration
FROM [Energies]
WHERE duration_mode = 'ON'
GROUP BY CAST([RecordDateTime] AS DATE)
ORDER BY CAST([RecordDateTime] AS DATE) DESC

 Datet      units   on_duration
-------------------------------
2020-01-17  3.53    758
2020-01-16  7.66    973
2020-01-15  15.12   1806
2020-01-13  10.4    500

ex op

date        units   on_duration    off_duration
-----------------------------------------------
2020-01-17  3.53    758            28
2020-01-16  7.66    973             9
2020-01-15  15.12   1806           96
2020-01-13  10.4    500            95

sample data

duration_mode   duration    RecordDateTime             units 
-------------------------------------------------------------
ON              187     2020-01-07 20:18:33.9744232     0.19
ON              187     2020-01-07 20:19:03.1554359     0.19
OFF             10      2020-01-07 20:22:13.5283932     0.00
ON              187     2020-01-07 20:24:39.0510166     0.19

Answer

I think that you are looking for conditional aggregation:

SELECT 
    CAST([RecordDateTime] AS DATE) as Date, 
    SUM(CAST([units] as float)) as Units, 
    SUM(CASE WHEN duration_mode = 'ON' THEN CAST(duration as int)/60 END) on_duration,
    SUM(CASE WHEN duration_mode = 'OFF' THEN CAST(duration as int)/60 END) off_duration
FROM [Energies]
GROUP BY CAST([RecordDateTime] AS DATE)
ORDER BY CAST([RecordDateTime] AS DATE) desc

Advertisement

Answer