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

Question

I have the following SQL query:

SELECT date(created_at), sum(duration) as total_duration 
FROM "workouts" WHERE "workouts"."user_id" = 5 AND "workouts"."category" = 'All' 
GROUP BY date(created_at) ORDER BY date(created_at) ASC

but I also want to query for the id of the workout, so I tried this:

SELECT id as id, date(created_at), sum(duration) as total_duration 
FROM "workouts" WHERE "workouts"."user_id" = 5 AND "workouts"."category" = 'All' 
GROUP BY id, date(created_at) ORDER BY date(created_at) ASC

However, this results in the group by date clause not working (i.e not summing the duration for all workouts on a specific date). I think this is because you cannot have one ID for a date that has multiple records. Is there any way to return the ID even where a specific record returned has multiple workouts associated with it?

For example, if someone had done 3 workouts yesterday, where each lasted 40 minutes in duration, the query would return 120 minutes (sums the durations for a given date) but then also returns each ID for the workouts on that date?

Or should I not do this in the query itself and just do it in the application?

Thanks for any help.

What database are you using? – Gordon Linoff May 08 '13 at 21:32 — Gordon Linoff, May 08 '13 at 21:32

score 1 · Answer 1 · answered May 08 '13 at 21:32

You should be able to use a subquery to get the result:

SELECT w1.id,
  w2.created_at
  coalesce(w2.total_duration, 0) total_duration
FROM "workouts" w1
INNER JOIN
(
  SELECT date(created_at) created_at, 
    sum(duration) as total_duration 
  FROM "workouts" 
  WHERE "workouts"."user_id" = 5 
    AND "workouts"."category" = 'All' 
  GROUP BY date(created_at)
) w2
  on w1.created_at = w2.created_at
ORDER BY w2.created_at;

If you want to return all IDs even those without a workout, then you could use a LEFT JOIN.

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

MySQL has group_concat(id) for that, but you ask about "sql" in general.

EDIT: For postgresql I have found Postgresql GROUP_CONCAT equivalent?

SELECT array_agg(id) as id_array, date(created_at), sum(duration) as total_duration 
FROM "workouts" WHERE "workouts"."user_id" = 5 AND "workouts"."category" = 'All' 
GROUP BY date(created_at) ORDER BY date(created_at) ASC

edited May 23 '17 at 11:49

Community

1
1

answered May 08 '13 at 21:33

flaschenpost

2,205
1
14
29

score 1 · Answer 3 · answered May 08 '13 at 21:34

You can use windows functions for this:

SELECT id as id, date(created_at), sum(duration) as total_duration,
       sum(sum(duration)) over (partition by date(created_at)) as DaysTotal
FROM "workouts"
WHERE "workouts"."user_id" = 5 AND "workouts"."category" = 'All' 
GROUP BY id, date(created_at)
ORDER BY date(created_at) ASC

This will add another column that is the total for the day.

score 0 · Accepted Answer · answered May 09 '13 at 19:27

Although @flaschenpost and @Gordon Linoff's answers were very helpful, I ended up needing aspects from both answers.

Here is how my query ended up:

SELECT array_agg(id) OVER (PARTITION BY date(created_at)) as ids, date(created_at), sum(load_volume) OVER (PARTITION BY date(created_at)) as total_load_volume 
FROM "workouts" WHERE "workouts"."user_id" = 5 AND "workouts"."category" = 'All'
GROUP BY date(created_at), id, load_volume ORDER BY date(created_at) ASC;

To get each workout id, if there were multiple workouts, on a given date I needed to use array_agg as well as a window function. This is the output:

                                  ids           |    date    | total_load_volume 
------------------------------------------------+------------+-------------------
 {30}                                           | 2013-04-20 |               400
 {29}                                           | 2013-04-23 |               400
 {31}                                           | 2013-04-24 |               400
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {33,34,35,36,37,38,41,42,43,44,45,46,47,48,49} | 2013-04-28 |              1732
 {50}                                           | 2013-04-30 |               400
 {51}                                           | 2013-05-07 |               400
(20 rows)

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

4 Answers4