Good evening, gentlemen!

Please help ...

There is a table:

name status amount ----------------------- val1 1 45643 val2 2 32115 val3 1 46431 val4 2 23366

Please tell me how to select 2 columns in the select below, count the amount for status = 1 in one, and status = 2 in the second, the total amount is considered in the select. Is it possible to do the required calculation without nested selects?

 select name, date, etc, wtf, sum(case when status !=1 then 1 else 0 end) as "2_count", sum(amount) as "all_amount" from foo, wtf, mega_table where /* over 9000 разных условий и связей тут */

Thank!

    2 answers 2

     select name, date, etc, wtf, sum(case when status =1 then amount else 0 end) as "status_1", sum(case when status =2 then amount else 0 end) as "status_2", sum(amount) as "all_amount" from foo, wtf, mega_table
    • Saw down the amount in then ... All ingenious is simple! Thank you very much! - sys1n4
    • It's my pleasure. This is a standard trick. - msi
     select (select sum(amount) from table where status = 1) as '1', (select sum(amount) from table where status = 2) as '2'
    • And without nested it is possible to do? For example, there is a select already with the conditions that are needed and already described in where: select name, date, etc, wt (sum when status! = 1 then 1 else 0 end) as all_count where / * over 9000 different conditions and connections here * / - sys1n4