How do I add values from different tables?

Question

How do I add values from different tables?

Navigation

#1 by (0 votes)

0

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

Follow the information in the two tables:

php sql codeigniter

asked by anonymous 24.05.2018 / 15:57

1 answer

Select data from three SQL tables Subselect OrderBy first line

score 0 · Answer 1

I do not think you need this other table. Maybe a view can be useful, it 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;