As I said in the title, I want to show all sum(bills) grouped by trimester and year but if there are no bills in one trimester show as: Trimester 2 amount = 0
My query (what I am trying):
select * from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
)
order by 1 desc, 2 asc
The result that I'm getting is this:
year Trimestre base iva total
2017 1er Trimestre 101 23 124
2016 1er Trimestre 10 2.1 12.1
2016 2º Trimestre 30 6.3 36.3
2016 3er Trimestre 10 2.1 12.1
2016 4º Trimestre 20 4.2 24.1
And what I really want is this:
year Trimestre base iva total
2017 1er Trimestre 101 23 124
2017 2º Trimestre 0 0 0
2017 3er Trimestre 0 0 0
2017 4º Trimestre 0 0 0
2016 1er Trimestre 10 2.1 12.1
2016 2º Trimestre 30 6.3 36.3
2016 3er Trimestre 10 2.1 12.1
2016 4º Trimestre 20 4.2 24.1
Thanks for reading, I'll be waiting for your help :)
Tip: 1er Trimestre = first trimester, 2º Trimestre = second trimester, 3er Trimestre = 3rd trimester and 4º Trimestre = 4th trimester.
Also tried this:
select * from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
) as "datos" right outer join (select distinct '1er Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '2º Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '3er Trimestre' as "Trimestre" from "Facturas"
union all
select distinct '4º Trimestre' as "Trimestre" from "Facturas" ) as "trimestres"
on "datos"."Trimestre" = "trimestres"."Trimestre"
order by 1 desc, 2 asc
This finally worked!:
select "year","Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (
select "year",'1er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 1 and 3 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'2º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 4 and 6 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'3er Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 7 and 9 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
select "year",'4º Trimestre' as "Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total" from (Select Year("p_fpagado") as "year",month("p_fpagado") as "month",COALESCE(sum("im_base"),0) as "base",COALESCE(sum("im_calculado"),0) as "iva",COALESCE(sum("im_total"),0) as "total" from "Facturas" where "p_pagado" = True and month("p_fpagado") between 10 and 12 group by Year("p_fpagado"),month("p_fpagado")) group by "year"
union all
(select distinct year("p_fpagado") as "year",'1er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'2º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'3er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'4º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0)
) group by "year","Trimestre" order by "year" desc,"Trimestre" asc
I made another "Union all" followed by:
(select distinct year("p_fpagado") as "year",'1er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'2º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'3er Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0
union all
select distinct year("p_fpagado") as "year",'4º Trimestre' as "Trimestre",0 as "base",0 as "iva",0 as "total" from "Facturas" having year("p_fpagado") != 0)
)
That is the table with all years that exists in the table where I want to get the values.
Then I grouped all with "()" and made as a table to select from there --> "()" and selected the following line:
select "year","Trimestre",COALESCE(sum("base"),0) as "base",COALESCE(sum("iva"),0) as "iva",COALESCE(sum("total"),0) as "total"
And I got it. Thanks all for trying to help me!
I hope this answer is helpful to anyone with a similar problem. Have a nice day :)