CODEWITHSUNDEEP
mysqlsqlsql-server
Jimmy Plage
Jimmy Plage

Reputation: 33

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 https://i.sstatic.net/c8VAX.jpg

Upvotes: 3

Views: 1063

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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

Upvotes: 2

Carlos Leyva
Carlos Leyva

Reputation: 99

use month function

select 
        YEAR(fecha) as Anno
        MONTH(fecha) as Mes,
        min(idcliente),
        min(nombre),
        sum(costo) as costo 
from actividad 
        inner join cliente on actividad.cliente_idcliente=cliente.idcliente 
where cliente_idcliente=82 
group by year(fecha), month(fecha)

Upvotes: 3

Related Questions