sql - Sum(column) and group by date but get all ID's for that date? -

i have following sql query:

select date(created_at), sum(duration) total_duration  "workouts" "workouts"."user_id" = 5 , "workouts"."category" = 'all'  group date(created_at) order date(created_at) asc 

but want query id of workout, tried this:

select id id, date(created_at), sum(duration) total_duration  "workouts" "workouts"."user_id" = 5 , "workouts"."category" = 'all'  group id, date(created_at) order date(created_at) asc 

however, results in group date clause not working (i.e not summing duration workouts on specific date). think because cannot have 1 id date has multiple records. there way return id specific record returned has multiple workouts associated it?

for example, if had done 3 workouts yesterday, each lasted 40 minutes in duration, query return 120 minutes (sums durations given date) returns each id workouts on date?

or should not in query , in application?

thanks help.

you should able use subquery result:

select w1.id,   w2.created_at   coalesce(w2.total_duration, 0) total_duration "workouts" w1 inner join (   select date(created_at) created_at,      sum(duration) total_duration    "workouts"    "workouts"."user_id" = 5      , "workouts"."category" = 'all'    group date(created_at) ) w2   on w1.created_at = w2.created_at order w2.created_at; 

if want return ids without workout, use left join.