How do I aggregate data into multiple different summed columns by criteria?

Question

I have data that looks like this:

| DateTime         | Category | Total |
|------------------|----------|-------|
| 3/23/18 9:00 AM  | Bork     | 1     |
| 3/23/18 9:15 AM  | Arf      | 2     |
| 3/23/18 9:30 AM  | Bork     | 1     |
| 3/23/18 9:45 AM  | Arf      | 2     |
| 3/23/18 10:00 AM | Bork     | 1     |
| 3/23/18 10:15 AM | Arf      | 2     |
| 3/23/18 10:30 AM | Bork     | 1     |
| 3/23/18 10:45 AM | Arf      | 2     |
| 3/23/18 11:00 AM | Bork     | 1     |
| 3/23/18 11:15 AM | Arf      | 2     |
| 3/23/18 11:30 AM | Bork     | 1     |
| 3/23/18 11:45 AM | Arf      | 2     |
| 3/23/18 12:00 PM | Bork     | 1     |
| 3/23/18 12:15 PM | Arf      | 2     |
| 3/23/18 12:30 PM | Bork     | 1     |
| 3/23/18 12:45 PM | Arf      | 2     |

And I want to sum by hour and keep the categories separate like this:

| Hour | Borks | Arfs |
|------|-------|------|
| 9    | 2     | 4    |
| 10   | 2     | 4    |
| 11   | 2     | 4    |
| 12   | 2     | 4    |

I know how to sum data by hour already... I use a query like this:

SELECT DatePart("h", DateTime), SUM(Total)
FROM Events
GROUP BY DatePart("h", DateTime), DateAdd("d", 0, DateDiff("d", 0, DateTime))

But how do I sum the categories into separate columns, and how do I name the columns in the output too?

Also, how do I make sure all hours are represented in the results, even if they have no data in the source? For example, it would have the hour listed and the total = 0.

You typically GROUP BY the columns you SELECT, except those who are arguments to set functions. — jarlh, Mar 26 '18 at 13:41
Use case expressions in the select list to do conditional aggregation. — jarlh, Mar 26 '18 at 13:42
If you DO KNOW the number of columns before running the script, that may be easy. Otherwise that can be a bit tough... Check [that](https://stackoverflow.com/a/12643408/8242325) answer. And please specify if you are using SQL Server, Oracle, MySQL, etc.. — Bruno Miquelin, Mar 26 '18 at 13:45

score 2 · Accepted Answer · edited Mar 26 '18 at 14:54

2

Use a pivot on the category:

SELECT
    DatePart(hour, DateTime),
    SUM(CASE WHEN Category = 'Bork' THEN Total ELSE 0 END) AS Borks,
    SUM(CASE WHEN Category = 'Arf'  THEN Total ELSE 0 END) AS Arfs
FROM Events
GROUP BY DatePart(hour, DateTime)
ORDER BY MIN(DateTime);

Note also that you current GROUP BY may not be what you intend. If you want to report hour groups, then group only by hours.

edited Mar 26 '18 at 14:54

Gordon Linoff

1,242,037
58
646
786

answered Mar 26 '18 at 13:42

Tim Biegeleisen

502,043
27
286
360

Works only in case he have ONLY those 2 categories. I guess he wants a dynamic query option – Bruno Miquelin Mar 26 '18 at 13:43
1

@BrunoMiquelin Did the OP indicate that there might be other categories? – Tim Biegeleisen Mar 26 '18 at 13:43
@BrunoMiquelin If OP wants to do that dynamically, he can check this [link](https://stackoverflow.com/a/18657579/2500920), he might get some idea about dynamic PIVOT. – Sathiya Kumar V M Mar 26 '18 at 14:27

How do I aggregate data into multiple different summed columns by criteria?

1 Answers1