Group by with calendar quarter as column

1
votes

I want the sum of one column of my table, grouped by calendar quarter. To be more specific, what I have now is the sum of the column I want month by month:

select month(emitido_date) as mes, year(emitido_date) as ano, ifnull(sum((det.preco * det.quantidade) * (iva.valor/100)),0) as totaliva
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 = 2 or  serie.documento_categoria_id = 3
group by mes, ano 
order by mes, ano desc

The code above is fine and works for me. It returns the sum of the column I want group by months. E.g.: January: 100, February: 200, March: 300. That's it until December.

My question is how to do this sum grouped by calendar quarter. To be specific, and looking for the example above, now I want to create a query which gives me the sum of values of [January, February, March]=600, [April, May, June], [July, August, September], [October, November, December]. I tried to do with the code below but I got an error:

1111 - invalid use of group function.

select 
sum(case when month(emitido_date) in (1,2,3) then ifnull(sum(det.quantidade),0) else 0 end) as Tri1,
sum(case when month(emitido_date) in (4,5,6) then ifnull(sum(det.quantidade),0) else 0 end) as Tri2,
    select 
    sum(case when month(emitido_date) in (1,2,3) then ifnull(sum((det.preco * det.quantidade) * (iva.valor/100)),0) else 0 end) as Tri1,
    sum(case when month(emitido_date) in (4,5,6) then ifnull(sum((det.preco * det.quantidade) * (iva.valor/100)),0) else 0 end) as Tri2,
    sum(case when month(emitido_date) in (7,8,9) then ifnull(sum((det.preco * det.quantidade) * (iva.valor/100)),0) else 0 end) as Tri3,
    sum(case when month(emitido_date) in (10,11,12) then ifnull(sum((det.preco * det.quantidade) * (iva.valor/100)),0) else 0 end) as Tri4,
    year(emitido_date) 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 year(emitido_date)

What's wrong?

2
mysqlsqlgroup-byaggregate-functionsmysql-error-1111
I don't know what 3 in 3 months means. If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. - Strawberry
"3 in 3 months" means the sum of one column in the periods: [Jan,mar],[apr,jun],[jul,sept],[oct,dec] - Hugo Machado
"3 in 3" is not how you say that in English. In this context it translates to "3 by 3 months", which is hard to combine in a sentence with "group by". A better way to say it might be "group by quarter". - Dan Getz
Sorry about my English. I had already edit the question. - Hugo Machado

2 Answers

1
votes

Try GROUP BY year(emitido_date) instead of group by ano.

Also replace ifnull(sum(det.quantidade),0) with ifnull(det.quantidade,0) as you already have SUM outside of CASE clause

0
votes

You cannot nest aggregation functions. But you don't need to. I think you want:

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