Mysql – Sequence of dates – months from January to December with SQL

mariadbMySQL

How can I create in SQL a function to search how many courses does a company each month from January to December in a specific year (2015)?

In the table fechahora form cursos, I have 1 in June, 1 in July, 3 in August, 1 in November and 0 in the other months. Furthermore I have the dates in this format '2015-01-01' and I need the name of the month.

I'm trying with this SQL statement.

SELECT 
  MONTHNAME(FECHAHORA) AS 'MONTH', 
  COUNT(Monthname(FECHAHORA)) AS 'TOTAL' 
FROM COURSES 
WHERE DATEOFCOURSE BETWEEN '2015-01-01' AND '2015-12-31' 
GROUP BY MES 
ORDER BY FECHAHORA;

From this statement, I have the results from the months that have a course but I need from January to December.

Best Answer

If you are using MariaDB, use something like this:

SELECT '2015-01-01' + INTERVAL seq MONTH FROM seq_0_to_11;

More discussion: https://mariadb.com/kb/en/library/sequence-storage-engine/

Related Solutions

Mysql – Next quarter without using Quarter function

mysql> SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * QUARTER(NOW()) + 1 - MONTH(NOW())
       MONTH;
--> 2016-07-01

If you don't need the date of the beginning of the next quarter, but need the string '3rd quarter July-September', then use a CASE clause.

Without QUARTER:

SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * FLOOR(MONTH(NOW())/3+1) + 1 - MONTH(NOW())
       MONTH;

Because QUARTER(x) is the same as FLOOR(MONTH(x)/3+1).

MySQL Date Sorting – Sorting by Month Chronologically

Try adding a specific field to order by. That field is the value of the month if it is greater than the value of current month, and add 12 if it is less (or equal), then order by that field:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-05-30 15:02:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from dates;
+------------+
| d          |
+------------+
| 2012-01-01 |
| 2008-01-01 |
| 2008-02-01 |
| 1987-01-02 |
| 1994-12-28 |
| 1987-05-02 |
| 1994-11-28 |
+------------+
7 rows in set (0.00 sec)

mysql> select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord;
+------------+----------+------+
| d          | month(d) | ord  |
+------------+----------+------+
| 1994-11-28 |       11 |   11 |
| 1994-12-28 |       12 |   12 |
| 2012-01-01 |        1 |   13 |
| 2008-01-01 |        1 |   13 |
| 1987-01-02 |        1 |   13 |
| 2008-02-01 |        2 |   14 |
| 1987-05-02 |        5 |   17 |
+------------+----------+------+
7 rows in set (0.00 sec)

mysql> select d from (select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord)x;
+------------+
| d          |
+------------+
| 1994-11-28 |
| 1994-12-28 |
| 2012-01-01 |
| 2008-01-01 |
| 1987-01-02 |
| 2008-02-01 |
| 1987-05-02 |
+------------+
7 rows in set (0.00 sec)

Best Answer

Related Solutions

Mysql – Next quarter without using Quarter function

MySQL Date Sorting – Sorting by Month Chronologically

Related Question