Separate query by month and add the results

Question

I have a database of requests that contain several requests for various dates, however I need to list a report by month and their respective sums in front.

Example:

The date field in the table is Date datetime ( requesteddata_hora_) and the other fields that will be summed as float ( deposistos,profit,supplier )

    $sql = "SELECT * FROM pedidos";
            $result = $mysqli->query($sql);
            while($row = $result->fetch_assoc()) { }

Answer 1

First I wouldn’t use float for this type of data. But to add up everything by month/year just use the 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 the dates and the SUM() will add the table data and the MONTHNAME() will return the name of the month. If you want the month names to be in English define the lc_time_names for pt_BR.

Answer 2

Just make a select with group by mes, you didn’t give much information about the problem but based on your image and the data you wrote, I made an example working for you in Sqlfiddle here

Follow the SQL code that was generated for direct view in this reply:

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