I have an order database that contains multiple requests for multiple dates, but I need to list a separate report per month and their respective sums up front.
Example:
ThedatefieldinthetableisasDatedatetime(date_time_order)andtheotherfieldsthatwillbeaddedasfloat(deposited,profit,vendor)
$sql="SELECT * FROM pedidos";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) { }
First I would not use float for this data type. But to add everything by month / year just use GROUP BY , for example:
SELECT SUM('depositos'),
SUM('lucro'),
SUM('fornecedor'),
MONTHNAME(data_hora_pedido) AS 'Mes'
FROM pedidos
GROUP BY YEAR('data_hora_pedido'),
MONTH('data_hora_pedido')
The YEAR(), MONTH() will group by dates and the SUM() will sum the table data and the MONTHNAME() will return the name of the month. If you want the month names to be in Portuguese, set lc_time_names to pt_BR .
Just do a select with group by mes, you did not give much information about the problem but based on your image and the data you wrote, I made an example working for you no SQLFiddle here
Here is the SQL code that was generated for direct visualization in this response:
select sum(depositos) as total_depositos,
sum(lucro) as total_lucro,
sum(fornecedor) as total_fornecedor,
depositos,
lucro,
fornecedor,
CASE extract(MONTH from data_hora_pedido)
WHEN 1 THEN 'Janeiro'
WHEN 2 THEN 'Fevereiro'
WHEN 3 THEN 'Março'
WHEN 4 THEN 'Abril'
WHEN 5 THEN 'Maio'
WHEN 6 THEN 'Junho'
WHEN 7 THEN 'Julho'
WHEN 8 THEN 'Agosto'
WHEN 9 THEN 'Setembro'
WHEN 10 THEN 'Outubro'
WHEN 11 THEN 'Novembro'
WHEN 12 THEN 'Dezembro'
END AS mes
from pedidos
group by mes