1

I am trying to make an awesome custom table which counts the amount of rows and organises them so that if there are three rows with a date in January 2013, four in March 2014 and five in October 2014 the table would show up as:

     Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2013 3   0   0   0   0   0   0   0   0   0   0   0
2014 0   0   4   0   0   0   0   0   0   5   0   0
user1877408
  • 137
  • 1
  • 11

1 Answers1

0

I would recommend using a view, rather than a new table, this way when your underlying data changes your new table won't be out of sync.

Since you have not given much sample data I have had to assume a structure, but you would want something like this:

CREATE TABLE T (`Date` DATETIME);
INSERT T (`Date`)
VALUES 
    ('2013-01-01'), ('2013-01-02'), ('2013-01-03'), 
    ('2014-03-01'), ('2014-03-02'), ('2014-03-03'),
    ('2014-10-01'), ('2014-10-01'), ('2014-10-01'),
    ('2014-10-01'), ('2014-10-01');

CREATE VIEW V
AS
    SELECT  YEAR(`Date`) AS `Year`,
            COUNT(CASE WHEN MONTH(`Date`) = 1 THEN 1 END) AS `Jan`,
            COUNT(CASE WHEN MONTH(`Date`) = 2 THEN 1 END) AS `Feb`,
            COUNT(CASE WHEN MONTH(`Date`) = 3 THEN 1 END) AS `Mar`,
            COUNT(CASE WHEN MONTH(`Date`) = 4 THEN 1 END) AS `Apr`,
            COUNT(CASE WHEN MONTH(`Date`) = 5 THEN 1 END) AS `May`,
            COUNT(CASE WHEN MONTH(`Date`) = 6 THEN 1 END) AS `Jun`,
            COUNT(CASE WHEN MONTH(`Date`) = 7 THEN 1 END) AS `Jul`,
            COUNT(CASE WHEN MONTH(`Date`) = 8 THEN 1 END) AS `Aug`,
            COUNT(CASE WHEN MONTH(`Date`) = 9 THEN 1 END) AS `Sep`,
            COUNT(CASE WHEN MONTH(`Date`) = 10 THEN 1 END) AS `Oct`,
            COUNT(CASE WHEN MONTH(`Date`) = 11 THEN 1 END) AS `Nov`,
            COUNT(CASE WHEN MONTH(`Date`) = 12 THEN 1 END) AS `Dec`
    FROM    T
    GROUP BY YEAR(`Date`);

Example on SQL Fiddle

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • I'm not sure that will display months with 0 records. I'd use sum(case etc then 1 else 0 end) instead of count. – Dan Bracuk Jul 29 '13 at 16:11
  • @DanBracuk It makes no difference (check the fiddle, it displays exactly as is laid out in the question), the two methods work exactly the same, it is simply personal preference. I personally think it makes more sense to use `COUNT` when you want a `COUNT`, and `SUM` when you want a SUM. – GarethD Jul 29 '13 at 16:18
  • for more on `COUNT(CASE...)` vs SUM(CASE ...)` http://dba.stackexchange.com/q/27558/7257 – GarethD Jul 29 '13 at 16:21
  • Sorry for my ignorance, but please could you explain how this would work as I am clueless – user1877408 Jul 29 '13 at 17:00
  • Once you have created the view once, you can just refer to it and it will always be in the layout you have suggested. If you post your actual table structure I can try and adapt it from a generic example to something more tailored to your needs. – GarethD Jul 29 '13 at 17:06
  • Sorry again, but I understand the views. Its more of this line: CASE WHEN MONTH(`Date`) = 1 THEN 1 END – user1877408 Jul 29 '13 at 17:13
  • It is just stating that when the month of the date in question is 1 (january) then put `1` otherwise the result is `NULL`, and since `COUNT(NULL)` is 0 you only increment that column when the month matches, as it is also grouping by year, then each column is a count where the month number matches the number in the CASE statement. – GarethD Jul 29 '13 at 18:01