Combining Two Groups in SQL

Question

I am looking to combine metrics for two groups under one field, while the other groups within that field remain on their own. How do I go about this?

I am pulling operational metrics and grouping them by different pods. I have pods A, B, C and D. I want to combine pods B and C to get metrics for pod A, pod D and pods B_C combined.

SELECT pod, volume
FROM table
WHERE Date.closed_week BETWEEN STRFTIME_USEC(DATE_ADD(TIME_USEC_TO_WEEK(NOW(), 0), -4, 'Week'), '%F') AND STRFTIME_USEC(DATE_ADD(TIME_USEC_TO_WEEK(NOW(), 0), -1, 'Week'), '%F')
GROUP BY 1

The above gives me the volume for pods A,B,C and D. However, I am looking for a way to have the volumes for pod B and C combined into one row. (Obviously this isn't the my actual code but should be sufficient to get the point across.)

Answer 1

Assuming you are using sum to aggregate (not clear from the question), you can use sum case like so:

SELECT sum(case when pod = 'A' then volume else 0 end) as PodA
    , sum(case when pod in ('B', 'C') then volume else 0 end) as PodBC
    , sum(case when pod = 'D' then volume else 0 end) as PodD
FROM table
WHERE Date.closed_week BETWEEN STRFTIME_USEC(DATE_ADD(TIME_USEC_TO_WEEK(NOW(), 0), -4, 'Week'), '%F') AND STRFTIME_USEC(DATE_ADD(TIME_USEC_TO_WEEK(NOW(), 0), -1, 'Week'), '%F')