CODEWITHSUNDEEP
sqloracle-database
Jagadish
Jagadish

Reputation: 21

Sql Query Output in desired format

I have a tables orders having some data.

>SELECT * FROM ORDERS;
output
==============
order_id customer_ref order_date            product_id  quantity
1        AAA          18-OCT-2019 12:53:51  10          100
2        BBB          18-OCT-2019 12:54:22  20          100
3        CCC          18-OCT-2019 12:55:07  10          100
4        AAA          18-OCT-2019 12:55:36  20          100
5        DDD          18-OCT-2019 12:55:54  30          100
6        EEE          18-OCT-2019 12:56:18  20          100
7        BBB          18-OCT-2019 12:56:36  20          200
8        FFF          18-OCT-2019 12:59:10  40          500
9        BBB          18-OCT-2019 12:59:10  40          200

i want output

customer_ref product_id count(*)
AAA 10  1
    20  1
BBB 20  2
    40  1
CCC 10  1
DDD 30  1
EEE 20  1
    40  1

I tried:

SELECT CUSTOMER_REF,PRODUCT_ID,COUNT(*)
FROM ORDERS
GROUP BY CUSTOMER_REF,PRODUCT_ID;

OUTPUT 
==========
CUSTOMER_REF  PRODUCT_ID COUNT(*)
AAA 10  1
AAA 20  1
BBB 20  2
CCC 10  1
DDD 30  1
EEE 20  1
FFF 40  1

Upvotes: 0

Views: 93

Answers (2)

shubham
shubham

Reputation: 99

SELECT (CASE when counter>1 THEN null else customer_ref END) as  customer_ref , product_id , count
        FROM  
            (
              SELECT customer_ref,product_id,COUNT(*) AS count,
                     ROW_NUMBER () OVER (PARTITION BY customer_ref ORDER BY product_id) AS counter
              FROM ORDERS 
              GROUP BY customer_ref,product_id
            )

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use LAG analytical function as following.

Select 
   CASE WHEN LAG(CUSTOMER_REF) 
     OVER (PARTIRION BY CUSTOMER_REF ORDER BY PRODUCT_ID) 
      = CUSTOMER_REF 
        THEN NULL 
        ELSE CUSTOMER_REF 
   END AS CUSTOMER_REF,
   PRODUCT_ID,
   CNT,
   ROW_NUMBER() OVER (PARTIRION BY CUSTOMER_REF ORDER BY PRODUCT_ID) AS RN
From 
(SELECT CUSTOMER_REF,PRODUCT_ID,COUNT(*) as cnt
FROM ORDERS
GROUP BY CUSTOMER_REF,PRODUCT_ID)
ORDER BY RN;

Cheers!!

Upvotes: 1

Related Questions