How to count count customers that have only 1 product / all products-CodePudding

I have table like this:

id	name	product
1	Ben	x
2	Ann	y
3	Kate	y
4	John	x
4	John	y

I wonder:

How to count customers who have only x product?
How to count customers who have both products?

CodePudding user response：

You could use these two queries to get job done

How to count customers who have only x product?

select t.ID, t.NAME
from t
group by t.ID, t.NAME
having count(distinct t.PRODUCT) = 1 
  and max(t.PRODUCT) = 'x'
;

How to count customers who have both products? You could use this analytic solution for that

select distinct ID, NAME
from (
select t.ID, t.NAME, t.PRODUCT, count(distinct t.PRODUCT)over(partition by t.ID ) cnt
from t
where t.PRODUCT in ('x', 'y')
)
where cnt > 1

or you could also use this aggregate solution

select t.ID, t.NAME
from t
where t.PRODUCT in ('x', 'y')
group by t.ID, t.NAME
having listagg(t.PRODUCT, ',')within group (order by t.PRODUCT) like '%x,y%'
;

Here I used a comma separator for the listagg aggregate function because I assumed none of your product names contain a comma ",". Otherwise you need to choose properly your owner separator for listagg function.

demo

CodePudding user response：

For only x product:

SELECT COUNT(*) FROM TABLE_NAME
WHERE PRODUCT='x'

For both products:

SELECT COUNT(DISTINCT(NAME)) FROM TABLE_NAME

For customer that has only x product:

SELECT PRODUCT, COUNT(NAME) FROM TABLE_NAME 
WHERE PRODUCT='x' GROUP BY PRODUCT