Query to Get the "prix_total" per month in SQLite3-CodePudding

I have a table with the following attributes, "id_commande, id_client, id_agent, D_commande, Heure_commande, prix_total". D_commande represents DATE OF SALE prix_total represents the price of a sale.

when I tried to create a Query to Get the "prix_total" per month. I struggled. notice that I tried this query but I don't get the correct result:

SELECT sum(prix_total)
FROM commande
GROUP BY (
     SELECT strftime('%y','D_commande')
)

CodePudding user response：

You can use

SELECT strftime('%Y-%m',D_commande) AS month, 
       SUM(prix_total) AS prix_total
  FROM commande 
 GROUP BY strftime('%Y-%m',D_commande)
 ORDER BY month

in order to get the result partitioned by months

CodePudding user response：

SELECT sum(prix_total)
FROM commande
GROUP BY strftime('%Y',D_commande) 
       , strftime('%m',D_commande)

Here is a demo

And also you can order by year and month:

ORDER BY strftime('%Y',D_commande) 
       , strftime('%m',D_commande)