0

I have a some tables that I am storing records in

transactions
---
| id | type_id | value |     created_at      |
|----|---------|-------|---------------------|
| 1  |    3    |   10  | 2016-11-01 09:00:00 |
| 2  |    3    |   15  | 2016-11-01 10:00:00 |
| 3  |    3    |   10  | 2016-11-03 09:00:00 |
types
---
| id |  name  |
|----|--------|
|  3 | 'Type' |

What I want to get is the total (sum) of the transaction values per day since the beginning of time, with days with no records showing as 0:

results
---
| type | total |    date    |
|------|-------|------------|
| Type |  25   | 2016-11-01 |
| Type |  0    | 2016-11-02 |
| Type |  10   | 2016-11-03 |

I can do this pretty easily when only being interested in the results that have values but query doesn't return dates with no records:

SELECT
    `types`.`name` as `type`,
    SUM(`transactions`.`value`) as `total`,
    DATE_FORMAT(`transactions`.`created_at`) as `date`
FROM `transactions` 
JOIN `types` 
    ON `transactions`.`type_id` = `types`.`id` 
GROUP BY `date`, `types`.`id`

Unfortunately, I need to get all dates between a range and return the values, defaulting to zero. the range could be variable depending on what the user selects.

Andrew Willis
  • 2,289
  • 3
  • 26
  • 53
  • 4
    Possible duplicate of [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) or http://stackoverflow.com/questions/6551179/mysql-find-missing-dates-between-a-date-range for a 2nd approach not involving a table (But it's not as robust) – xQbert Nov 22 '16 at 14:18
  • 1
    If are you doing a lot of date transactions, a calendar table will be very helpful. See this article, http://www.brianshowalter.com/calendar_tables, and create a calendar table. Then do appropriate joins to get the result you desire – zedfoxus Nov 22 '16 at 14:21
  • or a 3rd approach: http://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present SQL selects data from tables, it can't select data if it doesn't exist. so all of these approaches involve creating a dataset that can be joined to so as all dates between a date range exist. – xQbert Nov 22 '16 at 14:23

0 Answers0