Can you improve these 12 selections where only one parameter changes?

Navigation
0

I have an online table with about 3 thousand records. I make twelve selects to list the results per month, eg: 

select count(campo) from tabela where extract(year from data) = 2016 and extract(month from data) = 1;
select count(campo) from tabela where extract(year from data) = 2016 and extract(month from data) = 2;
select count(campo) from tabela where extract(year from data) = 2016 and extract(month from data) = 3;

I use PHP and want to know if it is feasible to do so: 

select * from tabela where extract(year from data) = 2016;
select count(campo) from RESULTADO_ANTERIOR extract(month from data) = 1;
select count(campo) from RESULTADO_ANTERIOR extract(month from data) = 2;
select count(campo) from RESULTADO_ANTERIOR extract(month from data) = 3;

If it's better to do so, can you give me an example of how to do it?

Note that the table will be much larger when I add other data I have, since it is data from 2006 until today.

edit the report should list all live births in a specific year, eg: 

         JAN|FEV|MAR|ABR|MAI|JUN|JUL|AGO|SET|OUT|NOV|DEZ
FEMININO  1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
MASCULINO 1 | 3 | 5 | 18| 3 | 4 | 4 | 45| 34| 4 | 34| 23
MENOR 2KG 1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
MAIOR 2KG 1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
HOSPITAL  1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
OUTROS    1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
MAE <18ANO1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23
MAR >18ANO1 | 3 | 5 | 18| 3 | 2 | 4 | 45| 34| 4 | 34| 23

I hope you understand. the way I do now, I compute each field individually using repeating structures, changing the months, but it's taking so long: /

Hugs.

    
asked by anonymous 26.10.2016 / 19:16

1 answer

2

Solved with the help of Bacco. the final code looks like this: 

SELECT COUNT(*) total, MONTH(data) mes 
FROM tabela
WHERE YEAR(data) =  "2016"
GROUP BY MONTH(data)

I did not need to use a select within another, so it was much faster.

    
26.10.2016 / 19:55
Best way to access activity components - MVP Error in the language of the month Django