Trying to figure out a query which shows the number of customer having 1,2 and more than 3 products. Here are the table name and fields:

• Product(prod_no, prod_cust_id)
• Customer(cust_id)

Product

Customer

Example of correct query I want to get is:

• 1 Product - 100 customer
• 2 Product - 52 customer
• 3 Products and above - 10 customer

I tried with the following query

SELECT COUNT (DISTINCT PROD_NO)"Product", CUST_ID"Customers"
  FROM PRODUCT, CUSTOMER
 WHERE PROD_CUST_ID = CUST_ID
HAVING COUNT(PROD_NO) >= 3 --for 3 products and above
GROUP BY CUST_ID

But the result is not what I wanted, so close yet so far. I tried only for 3 products and above, but how to add together with 1 product and 2 products.

Please help me out thanks

CodePudding user response：

You can first count the no of customers in the product table and then can count them separately. You can try the below query -

WITH DATA AS (SELECT P.*, COUNT(*) OVER(PARTITION BY prod_cust_id) CNT
                FROM Product P)
SELECT '1' Product, COUNT(CASE WHEN CNT = 1 THEN CNT ELSE NULL END) Customers
  FROM DATA
 UNION ALL
SELECT '2', COUNT(CASE WHEN CNT = 2 THEN CNT ELSE NULL END) 
  FROM DATA
 UNION ALL
SELECT '>=3', COUNT(CASE WHEN CNT >= 3 THEN CNT ELSE NULL END) 
  FROM DATA;
[Demo.][1]


  [1]: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=62768ac889f8292ade0b96012ad3a43f

CodePudding user response：

One option would be starting with distinctly counting by each column ( prod_no,prod_cust_id ), and evaluating the three or more products as an individual case within the conditional such as

WITH prod_cust AS
(
 SELECT COUNT(DISTINCT prod_no) AS prod_no, 
        DECODE( SIGN(COUNT(DISTINCT prod_cust_id)-2),1,'>=3',
                     COUNT(DISTINCT prod_cust_id) ) AS prod_cust_id
   FROM product
  GROUP BY prod_no 
)
SELECT prod_cust_id AS "Product", SUM(prod_no) AS "Customers"
  FROM prod_cust   
 GROUP BY prod_cust_id 
 ORDER BY 1

Demo