How to Fill Date Gaps in MySQL Aggregate Queries with Zeroes?

Filling Date Gaps in MySQL

Problem:

To extract aggregate data for certain dates, a MySQL query is used. However, the returned result set only includes dates that have data, leaving gaps between dates with no data. The goal is to fill these gaps with zeros to create a continuous range of dates.

Solution:

To accomplish this, a helper table is required. This table will contain all dates from the desired start date to the desired end date.

CREATE TABLE dates (
  dt DATE NOT NULL,
  PRIMARY KEY (dt)
);

The helper table can be populated using a query:

INSERT INTO dates (dt)
SELECT DATE(posted_at)
FROM messages
WHERE brand_id = 1
AND spam = 0
AND duplicate = 0
AND ignore = 0
GROUP BY DATE(posted_at)
UNION
SELECT DATE('2023-01-01') + INTERVAL i-1 DAY
FROM (
  SELECT 1 AS i 
  UNION ALL
  SELECT i + 1 FROM <table> WHERE i < DATEDIFF('2023-12-31', '2023-01-01')
) AS i;

Replace '2023-01-01' and '2023-12-31' with your desired start and end dates.

Next, the helper table is used to perform a LEFT JOIN with the original query:

SELECT  d.dt AS date,
        COUNT(*) AS total,
        SUM(attitude = 'positive') AS positive,
        SUM(attitude = 'neutral') AS neutral,
        SUM(attitude = 'negative') AS negative
FROM    dates d
LEFT JOIN
        messages m
ON      m.posted_at >= d.dt
        AND m.posted_at < d.dt + INTERVAL 1 DAYS
        AND spam = 0
        AND duplicate = 0
        AND ignore = 0
GROUP BY
        d.dt
ORDER BY
        d.dt

This modified query will return the desired result set with all dates, including those with no data in the original table, filled with zeros.

The above is the detailed content of How to Fill Date Gaps in MySQL Aggregate Queries with Zeroes?. For more information, please follow other related articles on the PHP Chinese website!