How can I group lines that are not familiar?

Question

My data is as follows: http://sqlfiddle.com/#!2/1266b2/1

But I want it to look like this:

+------------+------+---+ | 2015-01-01 | walk | 1 | | 2015-01-01 | run | 0 | | 2015-01-01 | bike | 0 | | 2015-01-02 | walk | 0 | | 2015-01-02 | run | 0 | | 2015-01-02 | bike | 0 | | 2015-01-03 | walk | 0 | | 2015-01-03 | run | 1 | | 2015-01-03 | bike | 0 | | 2015-01-04 | walk | 0 | | 2015-01-04 | run | 0 | | 2015-01-04 | bike | 0 | | 2015-01-05 | walk | 0 | | 2015-01-05 | run | 1 | | 2015-01-05 | bike | 0 | +------------+------+---+ 

Every day should have a collection of how many cases of each event arose.

• Dates are collected from the calendar table and are thus purely static.
• The names of the events are numerous and may change.
• Event responses combine events for a name and other contextual rules.

It would be very helpful to understand this. At the very least, offer tips for a better name (search terms) so that I can figure out how to solve this problem.

This is for processing charts at activezoo.com . Any recommendations on other approaches or methods of data analysis are very welcome.

Answer 1

It seems that you want every combination of date / event and you want to count the number of responses to events. If so, use the cross join between the calendar and event tables to get each combination, and then left join event_responses table and count the column from this table so that you only consider matches.

 SELECT calendar.date AS date, events.name AS event, COUNT(event_responses.date) AS count FROM events CROSS JOIN calendar LEFT JOIN event_responses ON event_responses.event_id = events.id AND event_responses.date = calendar.date GROUP BY calendar.date, event ORDER BY calendar.date, event 

http://sqlfiddle.com/#!2/d2560/1