group by semester/year PHP

Asked

Viewed 97 times

4

Hello, I have a table with date field, I want to group by semester/year, I did so.

SELECT
    SUM(quantidade) quantidade,
    YEAR(DATA) ano
FROM
    tbmanipulacao
GROUP BY
    YEAR(DATA),
    MONTH(DATA) <=6,
    MONTH(DATA) >6
ORDER BY
    ano
ASC

the return is like this: inserir a descrição da imagem aqui

the question is, how to identify the semester ? (know that that amount belongs to the 1st or 2nd semester).

php mysql sql

1 answer

3


To separate by semester, you need to generate this information in SELECT.
Taking the logic of your query, you can do IF(MONTH(data) <= 6, 1, 2) semestre.
This will generate "1" when the month is equal to or less than 6 and "2" to 7 or higher, then just use this information, which has the alias "semester" no group by:

SELECT
    SUM(quantidade) quantidade,
    YEAR(DATA) ano,
    IF(MONTH(data) <= 6, 1, 2) semestre
FROM
    teste
GROUP BY
    ano, semestre

Here a working example: http://sqlfiddle.com/#! 9/2aeb9c/10

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.