How do I add values from different tables?

Question

How do I add values from different tables?

Asked 6 years, 1 month ago

Viewed 51 times

0

I have two tables (Costs and Receipts) where I need to make one sum of the field containing values of each of them, after making a calculation (Balance) showing the balance of the Revenue - Costs = Balance.

Follow the information of the two tables:

the fields I need to make a sum are: recipe value and cost_value. All float type

– Ramiro

2018/05/24 at 13:59
@Virgilionovic I actually created a chart just for that.

– Ramiro

2018/05/24 at 13:59
I created a table called saldoprocesso to do the joins, but I doubt if this is necessary? and if it works by doing this too.

– Ramiro

2018/05/24 at 14:02

1 answer

Browser other questions tagged php sql codeigniter

You are not signed in. Login or sign up in order to post.

by Wilson Faustino • **829** points · Answer 1 · 2018-05-24T14:47:20+00:00

I think you don’t need this other table. Maybe a view can be useful, depends on your decision.

Try the SQL below:

SELECT
    CUSTAS_SOMA,
    RECEITAS_SOMA,
    (RECEITAS_SOMA-CUSTAS_SOMA) AS SALDO
FROM (
    SELECT
        (SELECT SUM(valor_custas) FROM custas ) AS CUSTAS_SOMA,
        (SELECT SUM(valor_receitas) FROM receitas) AS RECEITAS_SOMA
) AS SOMAS

In codeigniter, you can use:

$result=$this->db->query("
    SELECT
        CUSTAS_SOMA,
        RECEITAS_SOMA,
        (RECEITAS_SOMA-CUSTAS_SOMA) AS SALDO
    FROM (
        SELECT
            (SELECT SUM(valor_custas) FROM custas ) AS CUSTAS_SOMA,
            (SELECT SUM(valor_receitas) FROM receitas) AS RECEITAS_SOMA
    ) AS SOMAS
"));

$S=$result->first_row();

echo "Custos: ".$S->CUSTAS_SOMA."<br />";
echo "Receitas: ".$S->RECEITAS_SOMA."<br />";
echo "Saldo: ".$S->SALDO;