How to return a row for every date in SQL?-CodePudding

I want to retrieve the sum of transactions for every date from the last 7 days from my MySQL database, but some dates don't have any transactions. How do I return a 0 for those days?

Here is the SQL query I've worked on nd tried, but this one only gives those that do have a value for those days.

SELECT COUNT(transaction_id) AS orders, SUM(amount) AS sales, CAST(time AS DATE) AS time FROM tbltransactions WHERE time BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY CAST(time AS DATE) ORDER BY time ASC

CodePudding user response：

Try to

here is the test data:

CREATE TABLE tbltransactions (
  time DATE,
  transaction_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO tbltransactions (time, transaction_id, amount)
VALUES 
  ('2023-01-20', 1, 100.00),
  ('2023-01-21', 2, 200.00),
  ('2023-01-27', 3, 300.00),
  ('2023-01-29', 4, 400.00),
  ('2023-01-29', 5, 500.00);

CodePudding user response：

You can use a subquery with a SELECT statement to generate a list of all dates in the last 7 days, and then LEFT JOIN it with the transactions table. This will give you a result set with 0s for the dates without transactions:

SELECT  dates.date, COALESCE(SUM(transactio s.amount), 0) AS total_amount FROM (SELECT DATE_SUB(CURDATE(),INTERVAL (7   (@i := @i   1)) DAY) AS date  FROM    (SELECT @i := -1) r, information_schema.tables  LIMIT 7) dates LEFT JOIN transactions ON dates.date =DATE(transactions.transaction_date) GROUP BY dates.date ORDER BY dates.date DESC;