Tickets by priority and channel

The support lead wants a small cross-tab for the weekly review: priorities down the side, channels across the top.

Return one row per priority that appears in tickets, with:

column	type	meaning
priority	text	the ticket priority
chat	integer	tickets with channel = 'chat'
email	integer	tickets with channel = 'email'
web	integer	tickets with channel = 'web'
total	integer	all tickets with that priority, whatever the channel

Edge cases:

• A priority with no tickets in a channel shows 0 there, not NULL.
• If a new channel shows up, it doesn’t get a column. Its tickets still count in total.

Row order doesn’t matter.

Tables: tickets.

Approach

Group by the row label and compute one filtered count per output column.

SELECT priority,
       COUNT(*) FILTER (WHERE channel = 'chat')  AS chat,
       COUNT(*) FILTER (WHERE channel = 'email') AS email,
       COUNT(*) FILTER (WHERE channel = 'web')   AS web,
       COUNT(*)                                  AS total
FROM tickets
GROUP BY priority;

Why it works

Every ticket lands in exactly one priority group. Inside the group, each FILTER clause decides which tickets that column counts. COUNT of zero rows is 0, so an empty cell is 0 without any COALESCE.

The portable form is SUM(CASE WHEN channel = 'chat' THEN 1 ELSE 0 END). Note the ELSE 0. Without it, SUM over nothing but NULLs returns NULL, not 0.

DuckDB also has PIVOT tickets ON channel USING COUNT(*) GROUP BY priority. It’s shorter, but it creates one column per channel value it finds in the data, so the output shape changes when the data does. When a report has a fixed layout, write the columns out.

Under the hood

One hash aggregate, one pass over 800 rows. Each group carries four counters, and each row bumps total plus whichever filtered counter matches. The FILTER clause is evaluated per row before the counter update, so a pivot costs about the same as a plain GROUP BY.