0

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.

table and result

  • 3
    OK first - which RDBMS? Second, give us your table(s) in the form CREATE TABLE blah (field_1 type_1, ....., field_n type_n); and then INSERT INTO blah VALUES (value_1,.... , value_n); and then give us the result you want and the logic you used to get that result. Also, please use the editing tools that will make your post more legible - help us to help you! :-) – Vérace Apr 8 '18 at 15:24
  • Take a look here - please edit your question accordingly. – Vérace Apr 8 '18 at 17:31
2

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/

  • I like this answer to the question, seeing as the question is about MariaDB, and the answer is utilising a unique(?) MariaDB feature. To fully answer the question, you will of course have to join with the COURSES table in the question - something like SELECT month('2015-01-01' + INTERVAL seq MONTH), count(c.id) FROM seq_0_to_11 s LEFT OUTER JOIN COURSES c ON month(c.FECHAHORA) = month('2015-01-01' + INTERVAL seq MONTH) AND year(c.FECHAHORA) = 2015 GROUP BY 1 ORDER BY 1; – dbdemon Aug 6 '19 at 20:26
0

May be like this one:

SELECT m.MONTH, IFNULL(n.TOTAL,0) TOTAL FROM 
(SELECT 'January' AS MONTH
UNION 
SELECT 'February' AS MONTH
UNION 
SELECT 'March' AS MONTH
UNION 
SELECT 'April' AS MONTH
UNION 
SELECT 'May' AS MONTH
UNION 
SELECT 'June' AS MONTH
UNION 
SELECT 'July' AS MONTH
UNION 
SELECT 'August' AS MONTH
UNION 
SELECT 'September' AS MONTH
UNION 
SELECT 'October' AS MONTH
UNION 
SELECT 'November' AS MONTH
UNION 
SELECT 'December' AS MONTH
) m LEFT JOIN
(SELECT MONTHNAME(FECHAHORA) AS MONTH, 
COUNT(MONTHNAME(FECHAHORA)) AS TOTAL 
FROM COURSES 
WHERE DATEOFCOURSE BETWEEN '2015-01-01' AND '2015-12-31' 
GROUP BY MONTHNAME(FECHAHORA),MONTH(FECHAHORA)
ORDER BY MONTH(FECHAHORA)) n ON m.MONTH=n.MONTH;
  • 1
    I hate to point this out, but that's more of less what the OP has anyway. What he wants (AIUI) is to join these results onto all months and have a 0 total for those where there are no corresponding date/times. – Vérace Apr 9 '18 at 10:05
  • I have modified query for showing 0 count. – Pankaj Kumar Apr 9 '18 at 11:09
  • 1
    It's MariaDB - I don't think they have generate_series, but they do have this - joining to one of those babies ought to do the trick! Will check it out later if someone hasn't already posted by then! – Vérace Apr 9 '18 at 11:25
0

For completeness - and versions of MySQL < 8 - you could do this.

Create a table and dataset modelled on the graphic - in particular, over multiple years (2014 - 2016) as follows - I know that the OP mentioned only one year, but this is more generic and also it corresponds to the data in the screenshot!

Table and data:

CREATE TABLE my_course (course_date DATE NOT NULL, course_qty INTEGER NOT NULL);

INSERT INTO my_course VALUES ('2014-02-03', 15);   -- << Various years as per
INSERT INTO my_course VALUES ('2014-04-03',  2);   -- << screen shot - I also 
INSERT INTO my_course VALUES ('2014-04-07',  5);   -- << varied quantities!
INSERT INTO my_course VALUES ('2014-04-23',  1);
INSERT INTO my_course VALUES ('2015-06-03',  4);
INSERT INTO my_course VALUES ('2015-06-10',  3);
INSERT INTO my_course VALUES ('2015-07-12',  2);
INSERT INTO my_course VALUES ('2015-10-15', 16);
INSERT INTO my_course VALUES ('2015-10-27', 55);
INSERT INTO my_course VALUES ('2016-01-02', 56);
INSERT INTO my_course VALUES ('2016-09-03',  1);
INSERT INTO my_course VALUES ('2016-09-03', 89);

Then, you have to create a series of dates corresponding to all the months in those years - I shamelessly copied the date range construction code from here! For MariaDB, there's the interesting concept of the "sequence storage engine" - love the idea - hate the underscore syntax!

Then, you just JOIN these two tables using a LEFT JOIN (could do it the other way round) with the following SQL joining on both year and month. The db-fiddle is here.

SQL:

SELECT 
       x.my_year         AS "Course year", 
  LPAD(x.my_month, 2, 0) AS "Course month", -- << nice month display with LPAD
  COALESCE(y.courses_per_month, 0) AS "No. of courses"  
FROM
(
  SELECT YEAR(a.my_date) AS my_year, MONTH(a.my_date) AS my_month 
  FROM
  (
    SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH as my_date
    FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
WHERE a.my_date BETWEEN '2014-01-01' AND '2016-12-31'
) x
LEFT JOIN
(
  SELECT 
    YEAR(course_date) AS course_year, 
    MONTH(course_date) AS course_month,
    SUM(course_qty) AS courses_per_month
  FROM my_course
  GROUP BY YEAR(course_date), MONTH(course_date)
) y
ON 
  x.my_year = y.course_year
  AND
  x.my_month = y.course_month

Result:

Course year Course month    No. of courses
       2016           12                 0
       2016           11                 0
       2016           10                 0
       2016           09                90  -- << Note zero-padded months!
       2016           08                 0
< other results to beginning of 2014 snipped for brevity...>

The other "No. of courses" values all seemed OK!

0

If using PostgreSQL, this will work.

SELECT date_trunc('month', t.date), count(*)
FROM tbl AS t
WHERE t.date BETWEEN date '2015-1-1' AND (timestamp without time zone '2016-01-01' - interval '1 day' )
GROUP BY date_trunc('month', t.date)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.