Skip to content
Advertisement

CUSTOMER and purchased CATEGORY count details in Oracle SQL

I am having the below require. if anyone provide the solution it would be thankful

Requirements:-

Transaction of the customer who purchased in the below category Customers and purchased Category

Total Number of customer from category level category and customer count

I want to display the output life below output

Thanks R Sanjay

Advertisement

Answer

You can use cte, self join it and join it again with your table as following:

With cte as 
(Select category, count(1) as cnt
From your_table
Group by category)
Select t1.category,
T1.cnt,
T2.category,
T2.cnt,
Count(distinct t.customer) as both_cat
From cte t1
Join cte t2 
On (t1.category <> t2.category)
Join your_table t
On (t.category in (t1.category, t2.category)
Group by t1.category, t2.category,
         T1.cnt, t2.cnt
Order by t1.category, t2.category

Cheers!!

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement