crosstab in PostgreSQL, count

Question

Crosstab function returns error:

No function matches the given name and argument types

I have in table clients, dates and type of client.
Example:

CLIENT_ID | DATE    | CLI_TYPE
    1234  |  201601 | F
    1236  |  201602 | P
    1234  |  201602 | F
    1237  |  201601 | F

I would like to get number of clients(distinct) group by date and then count all clients and sort them by client type (but types: P i F put in row and count client, if they are P or F)

Something like this:

DATE   | COUNT_CLIENT | P | F
201601 | 2            | 0 | 2
201602 | 2            | 1 | 1

Please show what you tried, and also your version of Postgres. And ideally also actual table definitions - complete `CREATE TABLE` statement with data types and constraints. — Erwin Brandstetter, May 31 '16 at 15:15

score 1 · Answer 1 · edited May 23 '17 at 12:22

SELECT date
     , count(DISTINCT client_id) AS count_client
     , count(*) FILTER (WHERE cli_type = 'P') AS p
     , count(*) FILTER (WHERE cli_type = 'F') AS f
FROM   clients
GROUP  BY date;

This counts distinct clients per day, and total rows for client_types 'P' and 'F'. It's undefined how you want to count multiple types for the same client (or whether that's even possible).

About aggregate FILTER:

Postgres COUNT number of column values with INNER JOIN

crosstab() might make it faster, but it's pretty unclear what you want exactly.
About crosstab():

PostgreSQL Crosstab Query

crosstab in PostgreSQL, count

1 Answers1