Mysql - it´s possible use Group By semester?

Question

I need to use a group by per semester in a query. Anyone know any way to do this?

If I use:

 `HAVING Date Between '2012-01 'and '2012-06'`

I believe it will work, but will not be the correct result for the query.

Any help is appreciated

Please post your full query and table schema's - having people randomly guess what they are is not going to help people answer your question. Oh and adding - also what you want your result set to look like is good too — Adrian Cornish, Aug 13 '12 at 23:25

Devart · Accepted Answer · 2012-08-17T15:42:22.680

9

You can use this one -

+------+------------+
| id   | date       |
+------+------------+
| 1    | 2012-08-14 |
| 2    | 2012-09-20 |
| 3    | 2012-05-14 |
| 4    | 2012-05-08 |
| 5    | 2012-08-16 |
+------+------------+

INSERT INTO table_name VALUES 
  (1, '2012-08-14'),
  (2, '2012-09-20'),
  (3, '2012-05-14'),
  (4, '2012-05-08'),
  (5, '2012-08-16');

SELECT
  YEAR(date) year,
  IF(MONTH(date) < 7, 1, 2) semester,
  COUNT(*)
FROM
  table_name
GROUP BY
  year, semester;

+------+----------+----------+
| year | semester | COUNT(*) |
+------+----------+----------+
| 2012 |        1 |        2 |
| 2012 |        2 |        3 |
+------+----------+----------+

Using HAVING clause -

SELECT
  YEAR(date) year,
  IF(MONTH(date) < 7, 1, 2) semester,
  COUNT(*)
FROM
  table_name
GROUP BY
  year, semester
HAVING
  semester = 1

+------+----------+----------+
| year | semester | COUNT(*) |
+------+----------+----------+
| 2012 |        1 |        2 |
+------+----------+----------+

edited Aug 17 '12 at 15:42

answered Aug 14 '12 at 09:39

Devart

119,203
23
166
186

This query works to me, but if use a having clause, don´t works fine, return 0 results: `SELECT YEAR(date) year, IF(MONTH(date) < 7, 1, 2) semester, COUNT(*) FROM table_name GROUP BY year, semester HAVING semester = 1` – Bruno Aug 15 '12 at 16:09
I try to do with `HAVING semester = 1`, but don´t works for me. I think this is weird, but it worked that way: `HAVING semester < 2` (first semester) Or `HAVING semester > 1` (second semester) anyway, thanks for the help. – Bruno Aug 17 '12 at 15:26
Hm, it should work. I added INSERT statement to example. Try all example as is. – Devart Aug 17 '12 at 15:43

score 1 · Answer 2 · answered Jan 02 '18 at 02:43

1

You cant get any date semester with:

FLOOR( ( MONTH(date)-1) / 6 ) + 1

answered Jan 02 '18 at 02:43

glerendegui

1,457
13
15

score 0 · Answer 3 · edited Aug 18 '12 at 08:36

0

I managed to solve the problem of getting only the first semester or second semester just as follows.

To get only first semester:

SELECT
  YEAR(date) year,
  IF(MONTH(date) < 7, 1, 2) semester,
  COUNT(*)
FROM
  table_name
WHERE 
  MONTH(date) NOT IN(7,8,9,10,11,12)    
GROUP BY
  year, semester;

Thanks to all that helped.

edited Aug 18 '12 at 08:36

nhahtdh

55,989
15
126
162

answered Aug 15 '12 at 16:32

Bruno

99
1
8

Mysql - it´s possible use Group By semester?

3 Answers3