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. SELECT 
  2.     EXTRACT(MONTH FROM `date_at`) AS `month`,  
  3.     EXTRACT(YEAR FROM `date_at`) AS `year`,
  4.     SUM(`number`) AS `clicks`
  5. FROM `statistics_clicks`
  6. GROUP BY 
  7.     `month`, 
  8.     `year`
  9. ORDER BY 
  10.     `date_at` DESC
  11. LIMIT 2;
Programming Language: MySQL