Calculate percentage MySQL

Question

Calculate percentage MySQL

Navigation

#1 by (2 votes)

2

I want to do a percentage calculation. Taking the value that was received in that selected month (received = 1), divide by the total value of that selected month, and multiply by 100 to return the percentage. The logic I used below is working perfectly:

SET @total = (SELECT SUM(valor_receita) FROM receitas WHERE YEAR(data_vencimento) <= '2017' AND MONTH(data_vencimento) <= '06' AND id_usuario = 1);
SELECT SUM(valor_receita) AS valor_receita, ((SUM(valor_receita)/@total)*100) AS total
FROM receitas
WHERE recebido = 1 AND YEAR(data_vencimento) <= '2017' AND MONTH(data_vencimento) <= '06' AND id_usuario = 1;

What I would like to know is if there is any way to simplify this query by doing a JOIN or something like this ... without having to repeat the month and year twice as well.

php mysql

asked by anonymous 18.06.2017 / 14:15

1 answer

Get all the dates of a day of the week Add a new value at the beginning of a pandas series

score 2 · Accepted Answer

Without changing much, you can do with a subselect:

SELECT 
SUM(r.valor_receita) AS valor_receita, 
((SUM(r.valor_receita)/(SELECT 
                            SUM(x.valor_receita) 
                        FROM receitas x 
                        WHERE YEAR(x.data_vencimento) <= YEAR(r.data_vencimento) 
                        AND MONTH(x.data_vencimento) <= MONTH(r.data_vencimento) 
                        AND x.id_usuario = r.id_usuario))*100) AS total
FROM receitas r
WHERE r.recebido = 1 
AND YEAR(r.data_vencimento) <= '2017' 
AND MONTH(r.data_vencimento) <= '06' 
AND r.id_usuario = 1;

Edit:

I do not know if the syntax is correct, I can not test, but I think it would look like this: Using another comparison for Month / Year and returning a total column.

SELECT 
    SUM(r.valor_receita) AS valor_receita, 
    ((SUM(r.valor_receita)/(SELECT 
                                SUM(x.valor_receita) 
                            FROM receitas x 
                            WHERE x.data_vencimento <= r.data_vencimento
                            AND x.id_usuario = r.id_usuario))*100) AS percentual,
    (SELECT 
        SUM(x.valor_receita) 
    FROM receitas x 
    WHERE x.data_vencimento <= r.data_vencimento
    AND x.id_usuario = r.id_usuario) as total
FROM receitas r
WHERE r.recebido = 1 
and Date_Format(r.data_vencimento,'Y-%c') <= STR_TO_DATE('2017-06', '%Y-%c') 
AND r.id_usuario = 1;