mySQL query to not skip "empty" results

Question

I have a query like this

SELECT count(Distinct name) as total, DATE_FORMAT(date_added, '%M %d, %Y') AS date_added 
FROM `submitted_changes` 
WHERE date_added >= NOW() - INTERVAL 1 WEEK 
GROUP BY DATE(date_added)

It works great and returns rows that have a nicely formatted date and a total. Basically, this represents the number of submissions per day.

The problem I have is dealing with days with 0 submissions. I don't want to skip these days, but rather have the date shown and 0 for the total. Is there a way to ensure that when I do the query above (which only includes dates from the past week [7 days]) that I always get 7 rows back?

*"The problem I have is dealing with days with 0 submissions."* - If by that you mean "if the row is empty", then you could add an additional clause checking if it is empty with `AND|OR` (subquery), if that's what the question is about. You can also use a `CASE`. — Funk Forty Niner, Jun 30 '17 at 16:11
Yes @Fred-ii- I suppose that's what I mean (I think) can you point me in the right direction for this subquery? — jonmrich, Jun 30 '17 at 16:14
Have a look at this Q&A https://stackoverflow.com/q/17832906/1415724 there could be something in there you can base yourself on. Also https://stackoverflow.com/q/5520500/1415724 — Funk Forty Niner, Jun 30 '17 at 16:16
Actually @Fred-ii- I don't think that's the same. Each row has a single submission with a date. In my query, I'm counting the rows and grouping by date. The problem is that it skips the dates in my results when there are no submission (no rows). Is that the same issue? — jonmrich, Jun 30 '17 at 16:16
This might help https://stackoverflow.com/questions/36402030/mysql-fill-missing-dates. I suggest using PHP to solve this issue because it will be an easier to read solution than solving this from MySQL itself. — jorgonor, Jun 30 '17 at 16:19
@jorgonor Good grief...the mySQL looks like a disaster. How would you handle with PHP? — jonmrich, Jun 30 '17 at 16:20
@jonmrich, in the question I quoted there is a simple solution in PHP, I would do it like that. You can even encapsulate it in a helper method if you have to reuse it through your application. — jorgonor, Jun 30 '17 at 16:21
You want the system to return to you dates that are not in the table? That's not how SQL works. It retrieves data from the database; databases don't make up data. You can accomplish your goal by using a numbers/calendar table or use user variables and generate the dates between dates https://stackoverflow.com/questions/36402030/mysql-fill-missing-dates another way would be to create an inline view that does 6 unions and 7 selects one for today, today-1 today-2 ... and use that as the main table and you left join your table guaranteeing all 7 days. — xQbert, Jun 30 '17 at 16:23
`OR date_added =''` if empty I guess. Hard to say. Can you edit your post to include the schema/values? I may not be able to solve it, but others may be able to @jonmrich if default values are `NULL` then you may have to use `OR date_added =NULL`. Again, hard to say; am kind of guessing here. — Funk Forty Niner, Jun 30 '17 at 16:23
Possible duplicate of [What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?](https://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe) — xQbert, Jun 30 '17 at 16:27

Dimgold · Answer 1 · 2017-06-30T18:12:26.177

To do this, you need an existence of a record per each date. Since this is not the case in your submitted_changes table - I'll suggest to create a date table (if you don't have it already).

Note - for the shortest version, check the last edit at the bottom:

Here is an example with a temporary table. First run:

CREATE TEMPORARY TABLE IF NOT EXISTS dates AS 
SELECT DATE(curdate()-num) as date_col
FROM
(
SELECT 0 as num
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6) sub

This will create a table with 7 relevant dates. Now left join it with your data:

SELECT 
      count(Distinct name) as total,
      DATE_FORMAT(date_col, '%M %d, %Y') AS date_added 
FROM dates LEFT JOIN submitted_changes 
               on (dates.date_col = DATE(submitted_changes.date_added))
GROUP BY date_col

You can also run it as a one-shot query (with no create statement):

SELECT 
      count(Distinct name) as total,
      DATE_FORMAT(date_col, '%M %d, %Y') AS date_added 
FROM 
(SELECT DATE(curdate()-num) as date_col
FROM
(
SELECT 0 as num
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6) sub) dates
LEFT JOIN submitted_changes 
               on (dates.date_col = DATE(submitted_changes.date_added))
GROUP BY date_col

Another approach is a permanent dim_date. Here is a sample code for static table (with more extra fields):

CREATE TABLE dim_date (
  id            int(11) NOT NULL AUTO_INCREMENT,
  date      date, 
  day       int(11), 
  month         int(11), 
  year      int(11),
  day_name  varchar(45),
  PRIMARY KEY (id),
  INDEX date_index (date)
)

and then populate it:

SET @currdate := "2015-01-01";
SET @enddate := "2025-01-01";

delimiter $$
DROP PROCEDURE IF EXISTS BuildDate$$
CREATE PROCEDURE BuildDate()
    BEGIN
            WHILE @currdate < @enddate DO
            INSERT INTO dim_date  (date, day, month, year, day_name)
            VALUES (
                    @currdate,  DAY(@currdate),  MONTH(@currdate), 
              YEAR(@currdate), DAYNAME(@currdate)
);
            SET @currdate := DATE_ADD(@currdate, INTERVAL 1 DAY);
END WHILE;
    END$$
CALL BuildDate();

Then you can finally run your query with a left join:

SELECT 
      count(Distinct name) as total,
      DATE_FORMAT(date, '%M %d, %Y') AS date_added 
FROM dim_date LEFT JOIN submitted_changes 
               on (dim_date.date = DATE(submitted_changes.date_added))
WHERE date >= NOW() - INTERVAL 1 WEEK 
GROUP BY date

This would return a line per each date, even if there are no records in submitted_changes for them.

Edit: another one-shot super short version inspired by this post:

SELECT 
      count(Distinct name) as total,
      DATE_FORMAT(date, '%M %d, %Y') AS date_added 
(SELECT date(curdate()-id%7) as date
FROM submitted_changes
GROUP BY num) dates LEFT JOIN submitted_changes 
              on (date.dates = DATE(submitted_changes.date_added))
GROUP BY date

mySQL query to not skip "empty" results

1 Answers1