SQL Basic: Group by function

September 27, 2022

I am trying to get a table that would outline two group by functions but having some minor difficulty.

select 
    to_char("CreateTime", 'YYYY-MM') as MonthYear,
    floor(sum("Time"))::integer / 60 as "#MinutesWorkouts",
    sum(case when "Type" = 29 then 1 else 0 end) as "#Streaming",
    (sum(case when "Type" = 29 then "Time" else 0 end))::integer / 60 as "StreamingMinutes",
    sum(case when "Type" = 9 then 1 else 0 end) as "#GuidedProgram",
    sum(case when "Type" = 28 then 1 else 0 end) as "#Tall"
from 
    match_history
WHERE
    "MachineId" = {{Machine_Id}}
GROUP by 
    to_char("CreateTime", 'YYYY-MM')

Ideally – I’d like to showcase Machine ID in the column along with the dates. Currently the result shows as (image link) as it only shows the monthyear as the group function. I would like to ensure it shows machine ID on the column too as I’d like to add multiple machine IDs.

enter image description here

>Solution :

Why not just add machine_id ?

select 
    to_char("CreateTime", 'YYYY-MM') as MonthYear,
    "MachineId",
    floor(sum("Time"))::integer / 60 as "#MinutesWorkouts",
    sum(case when "Type" = 29 then 1 else 0 end) as "#Streaming",
    (sum(case when "Type" = 29 then "Time" else 0 end))::integer / 60 as "StreamingMinutes",
    sum(case when "Type" = 9 then 1 else 0 end) as "#GuidedProgram",
    sum(case when "Type" = 28 then 1 else 0 end) as "#Tall"
from 
    match_history
GROUP by 
    to_char("CreateTime", 'YYYY-MM'), "MachineId"