Get the sum of MySQL query result-CodePudding

I have a table BILLS with ID_CONTRACT_INST, F_SUM, DT_EVENT(Date) Columns

And another table CONTRACTS with ID_CONTRACT_INST(primary key), V_EXIT_IDENT

My TASK: sum of F_SUM column from table BILLS where V_EXIT_IDENT from Table CONTRACTS = 1158

I get: All but the sum

select BILLS.ID_CONTRACT_INST, BILLS.F_SUM
from BILLS
where ID_CONTRACT_INST IN
(select ID_CONTRACT_INST from CONTRACTS
where V_EXIT_IDENT = 1158);

CodePudding user response：

If BILLS.ID_CONTRACT_INST is unique then use

select BILLS.ID_CONTRACT_INST, SUM(BILLS.F_SUM) F_SUM
from BILLS
where ID_CONTRACT_INST IN (select ID_CONTRACT_INST 
                           from CONTRACTS
                           where V_EXIT_IDENT = 1158)
GROUP BY BILLS.ID_CONTRACT_INST WITH ROLLUP;

In the output you will see additional row with BILLS.ID_CONTRACT_INST value of NULL and total sum in F_SUM column.

GROUP BY Modifiers

CodePudding user response：

This appears to simply need:

select sum(BILLS.F_SUM)
from CONTRACTS
left join BILLS using (ID_CONTRACT_INST)
where V_EXIT_IDENT = 1158

Unless you meant separate sums for each contract, which is:

select CONTRACTS.ID_CONTRACT_INST, sum(BILLS.F_SUM)
from CONTRACTS
left join BILLS using (ID_CONTRACT_INST)
where V_EXIT_IDENT = 1158
group by CONTRACTS.ID_CONTRACT_INST

Note that if you only want to include results for contracts that have at least one bill, you would use inner join instead of left join. (In the case of the first query, this would return no result rows instead of a zero if there were no bills found for any selected contract.)

CodePudding user response：

You need to apply the sum function on the values to be aggregated and group them by all your output table's unaggregated columns.

select BILLS.ID_CONTRACT_INST, sum(BILLS.F_SUM)
from BILLS
where ID_CONTRACT_INST IN
   (select ID_CONTRACT_INST from CONTRACTS
   where V_EXIT_IDENT = 1158)
group by BILLS.ID_CONTRACT_INST;

Alternatively, a left join works the same way:

select BILLS.ID_CONTRACT_INST, sum(BILLS.F_SUM)
from BILLS as b
left join CONTRACTS as c 
    ON  b.ID_CONTRACT_INST = c.ID_CONTRACT_INST
where V_EXIT_IDENT = 1158
group by BILLS.ID_CONTRACT_INST;