Group column sum by quarter

Question

I would like to group the sum of one column per quarter, that is every three months.

I have the following query which groups from month to month:

select month(data) as mes, year(data) as ano, ifnull(sum(det.quantidade),0) as total
    from documento as doc
    inner join documento_serie as serie on serie.id = doc.documento_serie_id
    inner join documento_detail as det on doc.id = det.documento_id
    inner join phos_iva as iva on iva.id = det.iva_id
    where serie.documento_categoria_id = 3 
    group by mes, ano 
    order by mes, ano desc

How can I group the sum of a column at the intervals of [January, March],[April, June],[July, September],[October, December]?

It should also be months for a single year.

Answer 1

select CASE WHEN MONTH(data) in (1,2,3) THEN 'Primeiro'
            WHEN MONTH(data) in (4,5,6) THEN 'Segundo'
            WHEN MONTH(data) in (7,8,9) THEN 'Terceiro'
            WHEN MONTH(data) in (10,11,12) THEN 'Quarto'
       END AS trimestre, 
       YEAR(data) as ano, 
       SUM(CASE WHEN MONTH(data) in (1,2,3) THEN det.quantidade 
                WHEN MONTH(data) in (4,5,6) THEN det.quantidade 
                WHEN MONTH(data) in (7,8,9) THEN det.quantidade 
                WHEN MONTH(data) in (10,11,12) THEN det.quantidade 
           END) as total
from documento as doc
inner join documento_serie as serie on serie.id = doc.documento_serie_id
inner join documento_detail as det on doc.id = det.documento_id
inner join phos_iva as iva on iva.id = det.iva_id
where serie.documento_categoria_id = 3 
group by ano, CASE WHEN MONTH(data) in (1,2,3) THEN 1
                   WHEN MONTH(data) in (4,5,6) THEN 2
                   WHEN MONTH(data) in (7,8,9) THEN 3
                   WHEN MONTH(data) in (10,11,12) THEN 4
              END
order by data desc

Answer 2

The syntax below works on SQL Server, you could easily adapt it to MYSQL, the important here is the concept:

select 

sum(case when month(data) in (1,2,3) then ifnull(det.quantidade,0) else 0 end) Tri1,
sum(case when month(data) in (4,5,6) then ifnull(det.quantidade,0) else 0 end) Tri2,
sum(case when month(data) in (7,8,9) then ifnull(det.quantidade,0) else 0 end) Tri3,
sum(case when month(data) in (10,11,12) then ifnull(det.quantidade,0) else 0 end) Tri4,
year(data) as ano

from documento as doc
inner join documento_serie as serie on serie.id = doc.documento_serie_id
inner join documento_detail as det on doc.id = det.documento_id
inner join phos_iva as iva on iva.id = det.iva_id
where serie.documento_categoria_id = 3 
group by ano 
order by ano desc

Explanation:

sum(case when month(data) in (1,2,3) then ifnull(det.quantidade,0) else 0 end) Tri1

The above code will add the value of det.quantidade when the month is 1,2 or 3, otherwise it will add 0.

This is done for each Quarter. Thus, this SQL will return 5 columns, being 1 for each Quarter and another for the Year.