Oracle SQL - Add missing dates into query for each unique value in another column

Question

I am trying to make a query in Oracle SQL from the below table and my goal is to add in the missing days for each store.

    +------------+-------+-------+
    |    Date    | Store | Sales |
    +------------+-------+-------+
    | 01/01/2018 | A     |    20 |
    | 01/03/2018 | A     |    30 |
    | 01/02/2018 | B     |    10 |
    | 01/03/2018 | B     |    40 |
    | 01/04/2018 | B     |    50 |
    | 01/01/2018 | C     |    20 |
    +------------+-------+-------+

Desired Output:

    +------------+-------+-------+
    |    Date    | Store | Sales |
    +------------+-------+-------+
    | 01/01/2018 | A     |    20 |
    | 01/02/2018 | A     |     0 |
    | 01/03/2018 | A     |    30 |
    | 01/04/2018 | A     |     0 |
    | 01/01/2018 | B     |     0 |
    | 01/02/2018 | B     |    10 |
    | 01/03/2018 | B     |    40 |
    | 01/04/2018 | B     |    50 |
    | 01/01/2018 | C     |    20 |
    | 01/02/2018 | C     |     0 |
    | 01/03/2018 | C     |     0 |
    | 01/04/2018 | C     |     0 |
    +------------+-------+-------+

I have the 4 days I need from this SO question, however I cannot make my output look like the desired output. Any ideas?

Gordon Linoff · Accepted Answer · 2018-11-06 02:18:53Z

0

Use a cross join to generate the rows and then left join to bring in the data:

select s.store, d.date, coalesce(t.sales, 0) as sales
from (select distinct store from t) s cross join
     (select distinct date from t) d left join
     t
     on t.store = s.store and t.date = t.date;

answered Nov 6, 2018 at 2:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Haze Over a year ago

Wow it only took you 1 min to reply! Thank you!!

Haze Over a year ago

It worked!! I spent HOURS on this. Thank you so much Gordon!

Tim Biegeleisen · Accepted Answer · 2018-11-06 02:22:21Z

0

One option is to join to a calendar table:

WITH calendar AS (
    SELECT date '2018-01-01' AS "Date" FROM dual UNION ALL
    SELECT date '2018-02-01' FROM dual UNION ALL
    SELECT date '2018-03-01' FROM dual UNION ALL
    SELECT date '2018-04-01' FROM dual
)     

SELECT
    c."Date",
    s.Store,
    COALESCE(t.Sales, 0) AS Sales
FROM calendar c
CROSS JOIN (SELECT DISTINCT Store FROM yourTable) s
LEFT JOIN yourTable t
    ON t."Date" = c."Date" AND t.Store = s.Store;

Using a formal calendar table may be necessary here, because perhaps your current table data set does not contain all dates which you wish to appear in the final report.

answered Nov 6, 2018 at 2:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - Add missing dates into query for each unique value in another column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related