How to generate data in MySQL?

by

SQL is notoriously bad at returning data that is not in the database. You can find the beginning and ending values for gaps of dates, but getting all the dates is hard.

The solution is to create a calendar table with one record for each date and OUTER JOIN it to your query.

Here is an example assuming that created_at is type DATE:

SELECT calendar_date, COUNT(`id`)
FROM calendar LEFT OUTER JOIN my_table ON calendar.calendar_date = my_table.created_at
GROUP BY calendar_date

(I’m guessing that created_at is really DATETIME, so you’ll have to do a bit more gymnastics to JOIN the tables).

More Related Contents:

  1. If two columns are 1 then make new column
  2. How can I return pivot table output in MySQL?
  3. Using LIMIT within GROUP BY to get N results per group?
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Retrieving the last record in each group – MySQL
  6. Cast from VARCHAR to INT – MySQL
  7. Required to join 2 tables with their FKs in a 3rd table
  8. Does COUNT(*) always return a result?
  9. How to change the CHARACTER SET (and COLLATION) throughout a database?
  10. Connect By Prior Equivalent for MySQL
  11. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  12. How to convert all tables from MyISAM into InnoDB?
  13. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  14. SQL Query to show nearest date?
  15. How to create relationships in MySQL
  16. Select from one table where not in another
  17. MySQL Select last 7 days
  18. How to select the most recent set of dated records from a mysql table
  19. Second SELECT query if first SELECT returns 0 rows
  20. Set value to NULL in MySQL
  21. SELECT query return 1 row from each group
  22. MySql Single Table, Select last 7 days and include empty rows
  23. mysql SQL: specific item to be first and then to sort the rest of the items
  24. How to retrieve JSON data from MySQL?
  25. When is a timestamp (auto) updated?
  26. INSERT INTO Table from multiple tables
  27. Execute INSERT if table is empty?
  28. Mysql How do you create a clustered index?
  29. MySQL: Computed Column
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment