organize the months in SQL

Question

organize the months in SQL

Navigation

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

0

I'm running a SQL query to show me the total result for months by selecting the year but when it is executed it shows me the badly organized months and I used the ORDER BY function in DESC and ASC and do not want to organize, here I leave the SQL code and capture the query: it shows me the months in January, October, November, December, etc. and I want it to show January, February, March , April ..... etc.

SELECT DATE_FORMAT(STR_TO_DATE(mes_traslado,"%m") ,"%M") AS 
mes,AVG(conversion_ajust_fin) AS total FROM formulario_precebo WHERE 
año_destete = 2017 GROUP BY mes_traslado;

mysql sql

asked by Juan Esteban Yarce 26.02.2018 в 23:05

source

2 answers

Get points for a map of a promise and show them in the render Download and read PDF files on Android and IOS

score 0 · Answer 1

0

Try to sort using the alias you declared mes :

SELECT DATE_FORMAT(STR_TO_DATE(mes_traslado,"%m") ,"%M") AS 
mes,AVG(conversion_ajust_fin) AS total FROM formulario_precebo WHERE 
año_destete = 2017 ORDER BY mes ASC;

answered by 26.02.2018 в 23:56

score 0 · Answer 2

This is one of the problems that commented on in the note of my previous answer , when you do not use the right data type You are forced to live by converting the values through the use of supplementary functions, to obtain the desired results.

In this case, you will have to get another column converted to the numerical value of the month (by using %m ) and sort by that column.

Something like this:

SELECT 
    DATE_FORMAT(STR_TO_DATE(mes_traslado,"%m") ,"%M") AS mes,
    DATE_FORMAT(STR_TO_DATE(mes_traslado, '%m'), '%m') as mes_numero,   
    AVG(conversion_ajust_fin) AS total 
FROM formulario_precebo 
WHERE año_destete = 2017 
GROUP BY mes_traslado 
ORDER BY mes_numero;

If for some reason you are not interested in the results of a column called mes_numero , you can also do it this way (something ugly for my taste):

SELECT 
    DATE_FORMAT(STR_TO_DATE(mes_traslado,"%m") ,"%M") AS mes
    AVG(conversion_ajust_fin) AS total 
FROM formulario_precebo 
WHERE año_destete = 2017 
GROUP BY mes_traslado 
ORDER BY DATE_FORMAT(STR_TO_DATE(mes_traslado, '%m'), '%m');

That's what happens when you do not use an appropriate data type ...:)

I hope it serves you.