I have a sales chart that records the date and time of the sale.
The output format is this: "2018-09-02 15:00:08"
I wanted to get total sales per month but I'm having a hard time putting the query together. How can I filter only the month from a more complex data such as what I have?
The result should look something like this : January sales - 10 sales February - 5 sales
Table structure:
id | data_hora | total_pagamento
Just group by year and 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)
Roles
YEAR() - Returns the year of a date . MONTH() - Returns the month of a date . CONCAT() - Concatenates past values separated by , . LPAD() - Complete a string until it gets a number of characters. COUNT() - Count the rows returned. GROUP BY
You are responsible for grouping the query, in this case the year and the month. See more about it here .