Question 89371 in PostgreSQL Question posted in
The official documentation can be found here.

How can I create a Matrix in PosgreSQL with the same row and column headers from one Column and count distinct IDs as value? – Postgresql

I have a table with customer ids and product names as columns and I want to create a matrix with the product names so that I can see how many customer buy the same product combinations. I was not able to find a smooth build-in PosgreSQL-function for this use case. And I also didn’t find another way to that. Has someone an idea how I can create such a matrix?

My table:

customerId|productName
----------+-----------
1         | apple
2         | apple
3         | banana
1         | apple
1         | banana
3         | pizza

What I want:

(empty)|apple|banana|pizza
-------+-----+------+------
apple  | 2   |  1   |   0
banana | 1   |  2   |   1
pizza  | 0   |  1   |   1
Tags:

2

Answers


  1. 0

    I think you want something called Conditional aggregation :

    select customerId, 
       sum(case when productName = 'apple' then 1 else 0 end) as apple,
       sum(case when productName = 'banana' then 1 else 0 end) as banana,
       sum(case when productName = 'pizza' then 1 else 0 end) as pizza
from mytable
group by customerId
order by customerid;

    Result :

    customerid  apple   banana  pizza
1            2        1      0
2            1        0      0
3            0        1      1

    Demo here

    Login or Signup to reply.
  2. 0

    Do a self-join and aggregate using the filtered count function as the following:

    select t1.productname,
       count(distinct t2.customerid) filter (where t2.productname = 'apple') as apple,
       count(distinct t2.customerid) filter (where t2.productname = 'banana') as banana,
       count(distinct t2.customerid) filter (where t2.productname = 'pizza') as pizza
from table_name t1 join table_name t2
on t1.customerid = t2.customerid
group by t1.productname

    By this join, each bought product (X) for a customer will be joined to all of the bought products for that customer (including the X product itself), now we can easily count the number of customers who bought a combination of two products.

    The output for you sample data:

    productname    apple    banana  pizza
apple          2        1       0
banana         1        2       1
pizza          0        1       1

    See demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search