Query: sales per month with condition (MySQL)

Question

Query: sales per month with condition (MySQL)

Navigation

#1 by (1 votes)

2

I have a problem, maybe something simple, but it still does not work out.

This is the result to obtain

 SELECT month(sale_date) as mes, SUM(subtotal) as Totalizado 
 FROM sales 
 WHERE YEAR(sale_date) = '2018' 
 GROUP BY 1 order by 1

This brings me the totalized total of my sales, in sales I have a column status (PAID, MUST or CANCELED), I would list next to the columns as in the image, try to do it with case or if but I did not have success.

Thanks in advance. Greetings.

mysql

asked by JBAL27 26.06.2018 в 21:52

source

1 answer

How to choose a color / css Duda laravel eloquent sql

score 1 · Accepted Answer

SELECT  month(sale_date) as mes,
        SUM(subtotal) as Totalizado,
        SUM(CASE WHEN estado = 'PAGADA' THEN subtotal ELSE 0 END)  AS 'PAGADAS',
        SUM(CASE WHEN estado = 'DEBE' THEN subtotal ELSE 0 END)    AS 'DEBE',
        SUM(CASE WHEN estado = 'ANULADO' THEN subtotal ELSE 0 END) AS 'ANULADO'
    FROM sales 
    WHERE   YEAR(sale_date) = '2018' 
    GROUP BY month(sale_date) 
    order by month(sale_date)

You only have to condition each sum to the corresponding state by means of a CASE , doing, for example: SUM(CASE WHEN estado = 'PAGADA' THEN subtotal ELSE 0 END) . I suggest that if you show a Total, show all the subtotals that compose it, it usually generates confusion that these do not end up adding, so I added the column by ANULADO .