How to get total sales by months with mysql

Asked

Viewed 275 times

2

I have a sales chart that records the date and time of sale.

The output format is this:
"2018-09-02 15:00:08"

I wanted to get the total sales per month but I’m having difficulty to assemble the query. How can I filter only the month from a more complex data like the one I have?

The result should be similar to this:
January - 10 sales
February - 5 sales

Table structure:

id | data_hora | total_pagamento
mysql

1 answer

2


Just group by year and by month:

SELECT CONCAT(LPAD(MONTH(data_hora), 2, '0'), '/', YEAR(data_hora)) mes, COUNT(id) qtde
FROM nome_da_sua_tabela
GROUP BY YEAR(data_hora), MONTH(data_hora)

Function

  • YEAR() - Returns the year of a date.
  • MONTH() - Returns the month of a date.
  • CONCAT() - Concatenates the values passed separated by ,.
  • LPAD() - Completes a string until she gets a specific amount of characters.
  • COUNT() - Counts the returned lines.

GROUP BY

He is responsible for grouping the consultation, in this case by year and by month. See more about him here.

Browser other questions tagged mysql

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