Jimmy Plage Jimmy Plage - 3 months ago 6
MySQL Question

How to get the sum of sales per month?

How do I get the total amount per month of records from a field in a table.

I have 2 tables, customer and activity, within activity have cost (double) field and as foreign key customer id, want to show the customer and the sum of the cost given for a date.

I made the query to sum the cost value when the date is 2016-01, in this case January I want to show every month, January, February, March ....

select idcliente,
nombre,
sum(costo) as costo
from actividad
inner join cliente
on actividad.cliente_idcliente = cliente.idcliente
where cliente_idcliente = 82
group by fecha=2016-01;


and that this consultation applies to all my clients not just one

EXAMPLE: i want show a table like this http://imgur.com/a/6jYlB

Answer

It looks like you want a pivot query result, with the 12 months in a year being the columns, and each record corresponding to a given customer. Try the following query:

SELECT idcliente,
       SUM(CASE WHEN MONTH(fecha) = 1  THEN costo ELSE 0 END) AS January,
       SUM(CASE WHEN MONTH(fecha) = 2  THEN costo ELSE 0 END) AS February,
       SUM(CASE WHEN MONTH(fecha) = 3  THEN costo ELSE 0 END) AS March,
       SUM(CASE WHEN MONTH(fecha) = 4  THEN costo ELSE 0 END) AS April,
       SUM(CASE WHEN MONTH(fecha) = 5  THEN costo ELSE 0 END) AS May,
       SUM(CASE WHEN MONTH(fecha) = 6  THEN costo ELSE 0 END) AS June,
       SUM(CASE WHEN MONTH(fecha) = 7  THEN costo ELSE 0 END) AS July,
       SUM(CASE WHEN MONTH(fecha) = 8  THEN costo ELSE 0 END) AS August,
       SUM(CASE WHEN MONTH(fecha) = 9  THEN costo ELSE 0 END) AS September,
       SUM(CASE WHEN MONTH(fecha) = 10 THEN costo ELSE 0 END) AS October,
       SUM(CASE WHEN MONTH(fecha) = 11 THEN costo ELSE 0 END) AS November,
       SUM(CASE WHEN MONTH(fecha) = 12 THEN costo ELSE 0 END) AS December
FROM actividad
INNER JOIN cliente
    ON actividad.cliente_idcliente = cliente.idcliente
WHERE cliente_idcliente = 82 AND  -- remove this to see monthly summary for all customers
      YEAR(fecha) = 2016          -- change this to whatever year you want to see
GROUP BY idcliente