Sql Query Output in desired format

Question

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

I'm clear why the FFF would not be included in the expected output -- also are you sure that order id number 9 exists -- it should show up in your results if it does otherwise you must have used a different SQL statement — Hogan
– Hogan, Commented Oct 18, 2019 at 13:24
I will edit your title, since your question has absolutely nothing with "pivoting". Don't use technical terms you are not familiar with - you will confuse everybody. Also, use only the applicable tags. Your question has nothing to do with PL/SQL, or with your interface (whether it is SQL Developer or PL/SQL Developer). I edited your tags already. — user5683823
– user5683823, Commented Oct 18, 2019 at 14:13

Popeye · Accepted Answer · 2019-10-18 14:39:31Z

1

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!!

edited Oct 18, 2019 at 14:39

answered Oct 18, 2019 at 13:19

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shubham · Accepted Answer · 2019-10-19 10:46:19Z

0

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
            )

answered Oct 19, 2019 at 10:46

shubham

799 bronze badges

Collectives™ on Stack Overflow

Sql Query Output in desired format

i want output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

i want output

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related