25 April 2016

Group by date when using datetime datatype. In this snippet the grouping is used to pull out monthly statistics. The query is pretty much self explanatory. Group by in mysql can help you to bring your work with data to queries, thus avoiding creating multiple queries and working on your data in code. In production you could move dates from SELECT to GROUP BY, this way you can test your results.

Source code viewer
  1. EXTRACT(MONTH FROM `date_at`) AS `month`,
  2. EXTRACT(YEAR FROM `date_at`) AS `year`,
  3. SUM(`number`) AS `clicks`
  4. FROM `statistics_clicks`
  5. `month`,
  6. `year`
  7. `date_at` DESC
Programming Language: MySQL