Question 20290 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find the number of types of buyers for each product per user_id? – Mysql

I’m working on MySQL workbench 8.0 and I would like to find the number of types of buyers for each product per user_id. For instance, for chairs, if user_id 1, user_id 2, and user_id 3 make purchases only once, I would like to create a column (type of buyer), "BoughtOnce" and add the number of chairs that were purchased; 3 chairs (bought once by each user).

The sample data similar to the original one is shown below.

user_id Product
1 chair
1 bed
2 bed
1 chair
3 bed
2 sofa
3 sofa
3 shelf
2 chair

And I would like to find the following result:

Product BoughtOnce BoughtTwice
chair 1 2
bed 3 Null
sofa 2 Null
shelf 1 Null

For instance, user_id 1 bought 2 chairs and user_id 2 bought 1 chair.

I wanted to use the expression below but realized I could not aggregate a window function.

CASE WHEN COUNT(DISTINCT Prodcut) OVER (PARTITION BY user_id) = 1 Then …

Could anyone help me with this? I’d appreciate your help!

It’d be helpful if you suggest another approach as well.

Tags:

2

Answers


  1. 0

    With a CTE that gets the number of times one userid gets an item, you then count the occurances of the row_number for a product

    WITH CTE AS (SELECT 
`Product`, ROw_NUMBER() OVER(PARTITION BY `user_id`, `Product`) rn
FROM table1)
SELECT
  `Product`,
SUM(rn = 1) as once,
SUM(rn = 2) as twice,
SUM(rn = 3) as three,
SUM(rn > 3) as morethanthree
FROM CTE
GROUP BY `Product`
    Product once twice three morethanthree
    bed 3 0 0 0
    chair 2 1 0 0
    sofa 2 0 0 0
    shelf 1 0 0 0

    fiddle

    Login or Signup to reply.
  2. 0

    Twice grouping

    create table test (user_id int,Product varchar(20));
insert into test values
 (1,'chair')
,(1,'bed')
,(2,'bed')
,(1,'chair')
,(3,'bed')
,(2,'sofa')
,(3,'sofa')
,(3,'shelf')
,(2,'chair');
select product
   -- count number of products
  ,sum(case when qty=1 then qty end) BoughtOnce 
  ,sum(case when qty=2 then qty end) BoughtTwice
  ,sum(case when qty>2 then qty end) MoreThanTwice
   -- counted number of buyers
  ,sum(case when qty=1 then 1 end) BoughtOnce   
  ,sum(case when qty=2 then 1 end) BoughtTwice
  ,sum(case when qty>2 then 1 end) MoreThanTwice
from (select product,user_id,count(*) qty
       from test
       group by product,user_id
    ) t
group by product

    Result

    product BoughtOnce BoughtTwice MoreThanTwice BoughtOnce BoughtTwice MoreThanTwice
    chair 1 2 null 1 1 null
    bed 3 null null 3 null null
    sofa 2 null null 2 null null
    shelf 1 null null 1 null null

    Example

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

Back To Top
Search