Calculate Mysql percentage

Question

I’m looking for 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... without the need to also repeat twice the month and year.

Answer 1

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 have no way to test, but I believe it would look like this: Using another comparison for Month/Year and returning a column of total.

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;