Adding all dates to SQL query for grouped dates

Question

I would like SQL to return all dates, whether or not they are null or 0 from the following query:

   SELECT count(id) as Record_Count, delete_date as Date
   FROM table
   WHERE marketing_tag = 'XYZ'
   GROUP BY delete_date
   ORDER BY delete_date desc

Current Output:

| Record_Count  |    Date   |
|100            |   01/07/22|
|200            |   01/05/22|
|250            |   01/02/22|

Desired Output:

|  Record_Count |    Date   |
|100            |   01/07/22|
|0              |   01/06/22|
|200            |   01/05/22|
|0              |   01/04/22|
|0              |   01/03/22|
|250            |   01/02/22|

I do not have another table with dates to reference My min should be the first date pulled from the query (in this case, 01/02/22) and then my max should be the most recent date that the logic applies to (in this case, 01/07/22) is this possible without the creation of another table?

Mikhail Berlyant · Accepted Answer · 2022-02-22 17:25:28Z

1

Consider below approach

select ifnull(record_count, 0) record_count, date
from unnest((
  select generate_date_array(min(delete_date), max(delete_date)) 
  from your_table
)) date 
left join (
  select count(id) as record_count, delete_date as date 
  from your_table 
  where marketing_tag = 'xyz'
  group by delete_date
)
using (date)
order by date desc

if applied to sample data in your question - output is

answered Feb 22, 2022 at 17:25

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

mariah.davis Over a year ago

Thank you so much! This will work great, I just realized that the dates are inputted as timestamps instead of dates so I will need to convert first and then this will work. Many thanks!!

Collectives™ on Stack Overflow

Adding all dates to SQL query for grouped dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related