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.)

With the information given, how do you think people can help you? Table structure? Sample input? Expected output? Read this. https://stackoverflow.com/help/how-to-ask — Eric, Oct 10 '19 at 19:18
How are you combining B and C? Is that a sum, or strings being concatenated? — Barton Chittenden, Oct 10 '19 at 20:58

score 0 · Answer 1 · edited Oct 11 '19 at 01:22

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')

Combining Two Groups in SQL

1 Answers1