How to count when don't know specific types

Question

I'm working on a table kind of like this:

id	type
1	A
2	A
3	B
4	C
5	C

I wanted to count the number of ids for each type, and get a table like this.

type_a	type_b	type_c
2	1	2

What I did was

SELECT 
    SUM(CASE WHEN type = 'A' THEN 1 ELSE 0 END) AS type_a,
    SUM(CASE WHEN type = 'B' THEN 1 ELSE 0 END) AS type_b,
    SUM(CASE WHEN type = 'C' THEN 1 ELSE 0 END) AS type_c
FROM myTable

My question is, if I don't know how many types are there, and can't specificly list all cases, how can I achieve it?

It would have been much easier to return the result as two columns: Type, Count. — Janez Kuhar, Apr 16 '21 at 18:30
Does this answer your question? [Dynamic pivot query using PostgreSQL 9.3](https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3) — PM 77-1, Apr 16 '21 at 18:32

Erwin Brandstetter · Answer 1 · 2021-04-17T00:40:13.790

You are looking for "cross tabulation" or a "pivot table". I added tags.
However:

if I don't know how many types are there, and can't specifically list all cases, how can I achieve it?

Basically, that's impossible in a single SQL query because SQL demands to know the number of result columns at call time. It cannot return a dynamic number of columns on principle.

There are various workarounds with polymorphic types, or with a document type like json, jsonb, hstore or xml, or return arrays instead of individual columns ...

But to get exactly what you are asking for, an unknown number of dedicated columns, you need a two-step workflow. Like:

Build the query dynamically (determining the return type).
Execute it.

That said, if your case is simple and you deal with a hand full of known types, you can just over-provision. With a faster crosstab() query, or with simple conditional aggregation like you have it, just more elegant and efficient with the aggregate FILTER clause:

SELECT count(*) FILTER (WHERE type = 'A') AS type_a
     , count(*) FILTER (WHERE type = 'B') AS type_b
     , count(*) FILTER (WHERE type = 'C') AS type_c
     , count(*) FILTER (WHERE type = 'D') AS type_d
     -- that's all folks!
FROM   tbl;

Types with no entries report 0 (count() never returns NULL) which would be correct anyway.

Does not work for unknown types, obviously.

score 0 · Answer 2 · answered Apr 16 '21 at 23:26

0

If I understand you correctly, all you want is a simple COUNT:

SELECT
    type
    ,COUNT(id)
FROM myTable
GROUP BY type

answered Apr 16 '21 at 23:26

Don R

573
4
10

There are some more columns that I wants to add. For example: count, percentage, etc. and would like this to be, for each column, it is a type, for the first row, it's the count of each type, and for the second row, it's the percentage of each type. (percentage = count of each type / total counts of id). – Erin L Apr 16 '21 at 23:49

How to count when don't know specific types

2 Answers2