Group column sum by quarter

Question

Group column sum by quarter

Navigation

#1 by (1 votes)
#2 by (0 votes)

4

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

I have the following query that 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 do to group the sum of a column in the intervals of [January, March], [April, June], [July, September], [October, December]?

It should also be months for a single year.

mysql sql query

asked by anonymous 03.08.2015 / 15:39

2 answers

how to use the foreach Problem with slidebox and ng-repeat in the ionic framework

score 1 · 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

score 0 · Answer 2

The syntax below works in SQL Server, you could easily adapt it to MYSQL, the important thing 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.quantity 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, 1 for each Quarter and another for the Year.