CODEWITHSUNDEEP
mysqlsqlspagobi
Dhouha
Dhouha

Reputation: 741

how to order by month name chronologically instead of alphabetically?

I would like to get the month names to be in chronological order instead of alphabetically ordered. Here is my Sql code.

select  SUM(montant_ht)as Achat ,monthname(dim_date.date) as mois 
from operation_odoo  
join dim_date on operation_odoo.id_date_facturation = dim_date.id_date
where (operation_odoo.id_type_op=1) and (dim_date.Annee= ? or ? is null )
group by mois

Upvotes: 0

Views: 607

Answers (3)

Anand Pol
Anand Pol

Reputation: 1

in select query include additional column for month number and order by month number make sure you include same in group by as well

Sample Query : 

select current_Date,DATE_FORMAT(current_date,'%b') as 
m_name,DATE_FORMAT(current_date,'%m') as m_num
order by m_num

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You could use order by month()

    select  
        SUM(montant_ht)as Achat 
    ,monthname(dim_date.date) as mois 
    from operation_odoo  
    join dim_date on operation_odoo.id_date_facturation = dim_date.id_date
    where (operation_odoo.id_type_op=1) and (dim_date.Annee= ? or ? is null )
    group by mois
    order by month(dim_date.date)

for the SpagoBI try add also the column month() to the query 

select  
    SUM(montant_ht)as Achat 
,monthname(dim_date.date) as mois 
,  month(dim_date.date)
from operation_odoo  
join dim_date on operation_odoo.id_date_facturation = dim_date.id_date
where (operation_odoo.id_type_op=1) and (dim_date.Annee= ? or ? is null )
group by mois,  month(dim_date.date)
order by month(dim_date.date)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can order by the month. I would recommend:

order by month(max(dim_date.date))

As for your query, I would propose:

select monthname(d.date) as mois,
       sum(o.montant_ht)as Achat 
from operation_odoo o join
     dim_date d
     on o.id_date_facturation = d.id_date
where o.id_type_op = 1 and
      ( d.Annee= ? or ? is null )
group by mois
order by month(max(d.date));

Upvotes: 5

Related Questions