Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you get a sum() twice from one table with different where clause?

Tags:

sql

SELECT 
    sum(qty) as accept,
    (SELECT sum(qty) 
     FROM pile 
     WHERE pf=false) as reject 
FROM pile 
WHERE pf=true;

That's the SQL I use currently, but I'm guessing its not best practice? The other alternative I used before was SELECT sum(qty) FROM pile GROUP BY pf but I need them as columns and not as rows.

Are there any other solutions?

like image 453
lock Avatar asked Dec 21 '22 22:12

lock


2 Answers

SELECT pileTrue.sumTrue as accept, pileFalse.sumFalse as reject
FROM
(SELECT sum(qty) sumFalse FROM pile WHERE pf=false) as pileFalse,
(SELECT sum(qty) sumTrue  FROM pile WHERE pf=true ) as pileTrue
like image 24
eumiro Avatar answered Feb 09 '23 00:02

eumiro


Single pass through the table.

SELECT 
    sum(CASE WHEN pf = TRUE THEN qty ELSE 0 END) as accept,
    sum(CASE WHEN pf = FALSE THEN qty ELSE 0 END) as reject
FROM pile;
like image 110
beach Avatar answered Feb 08 '23 23:02

beach