[SOLVED] posgresql 10 How to assigned filtered sum result to different variables?

posgresql 10 How to assigned filtered sum result to different variables?

i try, within the same query from same table, assign the result of a sum based on different filter criteria.

Here my table toto istrue|weight| ------|------| false |0.1500| true |0.1319| true |0.0002| false |0.0001| true |0.0435|

A simple select give me the expected result.....

select (select sum(weight) from toto where istrue) as istrue,
    (select sum(weight) from toto where not istrue) as isnottrue;

|istrue |isnottrue |
|-------|----------|
|0.1756 |0.1501    |

now i want to assigne those 2 columns to 2 variables....

DO $$
declare
    var_istrue numeric(6,4) := 0.0;
    var_isnottrue numeric(6,4) := 0.0;
begin
    select (select sum(weight) from toto where istrue) as istrue,
            (select sum(weight) from toto where not istrue) as isnottrue 
        into var_istrue, 
            var_isnottrue;
    raise notice ' isTrue=%',var_istrue;
    raise notice ' isNOTTrue=%',var_isnottrue;
end $$;

and get the expected result:

00000:  isTrue=0.1756
00000:  isNOTTrue=0.1501

but i wonder if their is not a more efficient(faster) way to get the same outcome.

Thanks

Solution

You could use a single query/pass over the table with conditional aggregation:

select
    sum(weight) filter (where istrue) as istrue,
    sum(weight) filter (where not istrue) as isnottrue 
from toto
into var_istrue, var_isnottrue;