Sorry if my question’s title is confusing. It’ll be easier to look at my code/example below.
I am trying to sum each activity stored in my DB by each of their elapsed time (field value) and then group them by week. I feel like I’m close. Currently, this is my query:
SELECT week, activityType,
SUM (elapsedTime) AS activityTotalTime
FROM activity
WHERE year=2017
AND activityType IN ('rockclimbing', 'hike', 'run')
GROUP BY week, activityType;
This produces a result like:
week | activityType | activityTotalTime |
---|---|---|
1 | run | 2.22 |
2 | hike | 4.09 |
2 | run | 0.73 |
3 | hike | 1.2 |
3 | rockclimbing | 1.23 |
3 | run | 1.37 |
But what I would really like is:
week | run | hike | rockclimbing |
---|---|---|---|
1 | 2.22 | 0 | 0 |
2 | 0.72 | 4.09 | 0 |
3 | 1.37 | 4.3 | 0.67 |
How can I achieve this with SQL? Is this a case for a pivot? How to do that?