CODEWITHSUNDEEP
sqloracle-database
ArtF
ArtF

Reputation: 13

How to pull number of unique items sold in oracle sql

I have orders data for all items sold in my store for the past year. The table looks like this: 

order_date    item_id    item_name    order_quantity    unit_price
--------------------------------------------------------------------
01-01-2017    a123       a234         2                 10
04-02-2017    b123       b234         3                 12
04-09-2017    c123       c234         1                 15
04-10-2017    b123       b234         2                 12

I need to pull data for number of unique items sold by week, month, and quarter. The query output should look like this:

timeline number    unique_item_count
week      1         1
week      14        1
week      15        2
month     1         1
month     4         2
quarter   2         2

I've tried the following:

 SELECT 
  TO_CHAR(c.ORDER_DAY, 'Q') AS QTR,
  TO_CHAR(c.ORDER_DAY, 'MM') AS MNTH,
  TO_CHAR(c.ORDER_DAY, 'WW') AS WK,
  COUNT(DISTINCT CASE WHEN (c.QUANTITY*c.OUR_PRICE) > 0 THEN ITEM_ID ELSE NULL END) AS SALES_CNT
FROM TABLE c
GROUP BY TO_CHAR(c.ORDER_DAY, 'Q'), TO_CHAR(c.ORDER_DAY, 'MM'), TO_CHAR(c.ORDER_DAY, 'WW');

This works for weekly data, however, monthly and quarterly is just a sum of weekly numbers, which is incorrect in this case, since same items might be ordered in two different weeks, so the monthly number should be lower.

Is there a way to pull number of unique items purchased in each week, month, quarter?

Thanks!

Upvotes: 1

Views: 111

Answers (3)

MT0
MT0

Reputation: 168041

You can do it without using the union of multiple statements by using UNPIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_c (
  order_date,
  item_id,
  item_name,
  order_quantity,
  order_price
) AS
SELECT DATE '2017-09-01' + LEVEL * 3 - 3, 1, 'aaa', 1, 1 FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL
SELECT DATE '2017-09-01' + LEVEL * 4 - 4, 2, 'bbb', 1, 1 FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL
SELECT DATE '2017-09-01' + LEVEL * 5 - 5, 3, 'ccc', 1, 1 FROM DUAL CONNECT BY LEVEL <= 10

Query 1:

SELECT year,
       timeline,
       "number",
       COUNT( DISTINCT item_id )
FROM   (
  SELECT TO_CHAR( order_date, 'WW' ) AS week,
         TO_CHAR( order_date, 'MM' ) AS month,
         TO_CHAR( order_date, 'Q' ) AS quarter,
         EXTRACT( YEAR from order_date ) AS year,
         item_id
  FROM   table_c
  WHERE  order_quantity > 0
  AND    order_price > 0
)
UNPIVOT ( "number" FOR timeline IN ( week AS 'week', month AS 'month', quarter AS 'quarter' ) )
GROUP BY year, timeline, "number"
ORDER BY year, timeline, "number"

Results:

| YEAR | TIMELINE | number | COUNT(DISTINCTITEM_ID) |
|------|----------|--------|------------------------|
| 2017 |    month |     09 |                      3 |
| 2017 |    month |     10 |                      2 |
| 2017 |  quarter |      3 |                      3 |
| 2017 |  quarter |      4 |                      2 |
| 2017 |     week |     35 |                      3 |
| 2017 |     week |     36 |                      3 |
| 2017 |     week |     37 |                      3 |
| 2017 |     week |     38 |                      3 |
| 2017 |     week |     39 |                      3 |
| 2017 |     week |     40 |                      2 |
| 2017 |     week |     41 |                      1 |
| 2017 |     week |     42 |                      1 |

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17934

You can use GROUPING SETS to aggregate at various levels in a single query. Like so:

SELECT CASE
         WHEN GROUPING (TO_CHAR (c.order_day, 'Q')) = 0 THEN 'Quarter'
         WHEN GROUPING (TO_CHAR (c.order_day, 'MM')) = 0 THEN 'Month'
         WHEN GROUPING (TO_CHAR (c.order_day, 'WW')) = 0 THEN 'Week'
         ELSE '??'
       END
         timeline,
       CASE
         WHEN GROUPING (TO_CHAR (c.order_day, 'Q')) = 0 THEN TO_CHAR (c.order_day, 'Q')
         WHEN GROUPING (TO_CHAR (c.order_day, 'MM')) = 0 THEN TO_CHAR (c.order_day, 'MM')
         WHEN GROUPING (TO_CHAR (c.order_day, 'WW')) = 0 THEN TO_CHAR (c.order_day, 'WW')
         ELSE '??'
       END
         "NUMBER",
       COUNT (DISTINCT CASE WHEN (c.quantity * c.our_price) > 0 THEN item_id ELSE NULL END) unique_item_count
FROM   c
GROUP BY GROUPING SETS ( 
    (TO_CHAR (c.order_day, 'Q')), 
    (TO_CHAR (c.order_day, 'MM')), 
    (TO_CHAR (c.order_day, 'WW')))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this with union all:

SELECT 'Q' as timeline, TO_CHAR(c.ORDER_DAY, 'Q') AS number,
       COUNT(DISTINCT CASE WHEN (c.QUANTITY*c.OUR_PRICE) > 0 THEN ITEM_ID  END) AS SALES_CNT  
FROM TABLE c
GROUP BY TO_CHAR(c.ORDER_DAY, 'Q')
UNION ALL
SELECT 'MM' as timeline, TO_CHAR(c.ORDER_DAY, 'MM') AS number,
       COUNT(DISTINCT CASE WHEN (c.QUANTITY*c.OUR_PRICE) > 0 THEN ITEM_ID  END) AS SALES_CNT  
FROM TABLE c
GROUP BY TO_CHAR(c.ORDER_DAY, 'MM')
UNION ALL
SELECT 'WW' as timeline, TO_CHAR(c.ORDER_DAY, 'WW') AS number,
       COUNT(DISTINCT CASE WHEN (c.QUANTITY*c.OUR_PRICE) > 0 THEN ITEM_ID  END) AS SALES_CNT  
FROM TABLE c
GROUP BY TO_CHAR(c.ORDER_DAY, 'WW');

Upvotes: 0

Related Questions