CODEWITHSUNDEEP
sqloracle-databasegroup-by
ssbsts
ssbsts

Reputation: 844

SQL - Group By Query

Lets say I have a table that has the following columns:

customerId, productId

What I want to see is if I have 100,000 customers I want to group them so I can see X number of customers have Y number of products. For example 2,000 customers may have 5 purchases, 1,000 customers have 4 products, 10 customers might have 25 products, etc. How do I group based on number of customers with X number of products?

Database is Oracle.

Sample Data Set:

customerID  productId
---------- ----------
 12345      33035
 12345      33049
 12345      33054
 56789      32777
 56789      32897
 56789      32928
 56789      32958
 56789      33174
 56789      33175
 56789      33410
 56789      35101
 67890      32777
 67890      32897
 67890      32928
 67890      32958
 67890      33174
 67890      33175
 67890      33410
 67890      35101
  45678      33035
  45678      33289
  45678      34354
  45678      36094
 23456      32778
 23456      33047
 23456      33051
 34567      32776
 34567      32778
 34567      33162

This results in this grouping (based on example data set) where there are 3 customers with 3 products, 2 customers with 8 products and 1 customer with 4 products.

number_customers    number_products
3           3
2           8
1           4

I have tried a bunch of group by statements, but I am missing something. Any help would be greatly appreciated.

Thanks

Upvotes: 0

Views: 463

Answers (3)

Andrew C. Kee
Andrew C. Kee

Reputation: 79

create table cust_pur as select floor(dbms_random.value(1,21)) customer_id,         floor(dbms_random.value(1,21)) product_id from dual connect by level <= 100 order by customer_id

select * from cust_pur order by customer_id


with k1 as (
select distinct(customer_id), count(product_id)  over ( partition by customer_id ) cnt from cust_pur order by customer_id
)
select count(customer_id),cnt from k1 group by cnt

Upvotes: 0

Anon
Anon

Reputation: 10918

SELECT
  COUNT(CustomerID) AS number_customers,
  number_products
FROM (
  SELECT CustomerID, 
  COUNT(ProductID) AS number_products
  FROM tableName
  GROUP BY customerID
) subquery
GROUP BY number_products

Upvotes: 2

xQbert
xQbert

Reputation: 35343

Select CustomerID, count(ProductID)
FROM tableName
group by customerID
having count(ProductID) > 25

now if you want to only count distinct products...

   Select CustomerID, count(distinct ProductID)
    FROM tableName
    group by customerID
    having count(ProductID) > 25

Assuming your data contains customer 1 listed each time per product and that multiple customers may be tied to the same product.

Upvotes: 0

Related Questions