Can you improve these 12 selects where only one parameter varies?

Asked

Viewed 70 times

1

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

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;

PHP usage 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 it this way, you can give me an example of how to?

Obs: the table will get much bigger when I add other data I have, as it is data from 2006 until today.

Edit the report shall list all live births in a specific year, ex:

         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 calculate each field individually using repeating structures altering the months, but it’s taking too long :/

hugs.

php sql mysqli select
  • 1

    Related: Can subqueries decrease performance? Myth or truth?. I do not know the purpose of this consultation but I have the impression that grouping the result per year and month would solve.

  • 1

    I have to list all live births in a specific year and by category, I will post example in the question

  • 3

    This seems to me a case for a simple GROUP BY. By the way, this Extract is strange, what would be the reason not to use YEAR( data ) and MONTH( data ) ? Makes me feel like I’m using the wrong kind of field to need this.

  • I’m trying to group here, if I can, put the result

  • 2

    where extract(year from data) = 2016 GROUP BY extract(month from data)

  • @Bacco worked yes , just one more detail to solve: the months November and December that has not been born anyone, does not appear the number 0, has how to appear?

  • 1

    @Italorodrigo makes no sense to show up things that don’t exist. You can even create a situation with SQL, but unnecessarily complex, Much easier to do a for( $mes counting from 1 to 12 in PHP itself, and show zero where it did not work. These things pay to control in the exhibition.

  • did not know it was possible to group using Extract ^^

  • 1

    I find it strange to be using Extract :) - normally I would use MONTH( date );

  • @Bacco I can even get around this with PHP, but if the blank month is in the middle, ex: was only born in January and March. how do I know you weren’t born in February? has a way to show the month?

  • did not know the year(data) and Month(data) command, as I adapted the code of a Firebird database

  • 1

    Only include in return. COUNT(*), MONTH( data ) AS mes FROM ..., ai in PHP you use $Row['mes'] as the index of the array, to use in the loop from 1 to 12.

  • 1

    mass, I got it solved here. you helped a lot. how do I select your answer as chosen? the final code is as follows: select Count(nu), Month(data) from Sinasc Where year(date) = "2016" and municipio_ibge = "260500" group by Month(date)

  • 2

    The problem, Italo, is that your question was completely different from the way we removed it, so I solved it in the comments and not as an official answer. If you want, you can post in the field below your final solution without problems.

  • I’m new here, what should I do then to finish?

  • 2

    You can post the final code as a response in the field below, and mark as accepted if you like.

  • 1

    A few reading tips to make the most of the site: [Ask], What is the XY problem and Community FAQ

Show 12 more comments

1 answer

2


Solved with the help of Bacco. the final code was thus:

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

I didn’t need to use one select inside another, so it got much faster.

  • 2

    Then you can click the green V on the side of your reply, to mark as the accepted solution, so future visitors will see already in the listing of the main page that has been resolved.

Browser other questions tagged php sql mysqli select

You are not signed in. Login or sign up in order to post.