CODEWITHSUNDEEP
sqlpostgresql
Kevin
Kevin

Reputation: 6292

Combine two SQL select into one

I have two SQL SELECT COUNT statements:

SELECT COUNT(*) FROM table1 t1 
INNER JOIN table2 t2 ON t2.id = t1.rowid
WHERE t1.flag1 = false AND t2.flag2 = true;

SELECT COUNT(*) FROM table1 t1 
INNER JOIN table2 t2 ON t2.id = t1.rowid
WHERE t1.flag1 = true AND t2.flag2 = false;

As can be seen the only difference of these two statements are the flipped condition.

But what I want to do is to combine the two statements into one so that the output becomes one table with two columns, the first column contains the result of the first statement and the second column contains the result of the second statement. Something like:

   count1   |   count 2
-------------------------
    3456    |     9864

I use PostgreSQL by the way. Can someone let me know how to do this?

Many thanks

Upvotes: 1

Views: 177

Answers (3)

Tomas Greif
Tomas Greif

Reputation: 22643

If you really need it this way (use two sql queries and combine them) then:

select * from
 (SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.rowid WHERE t1.flag1 = false AND t2.flag2 = true) a,
 (SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.rowid WHERE t1.flag1 = true AND t2.flag2 = false) b

Based on your SQL, this would be better solution:

select
    sum (case when not t1.flag1 and t2.flag2 then 1 else 0 end) as count1,
    sum (case when t1.flag1 and not t2.flag2 then 1 else 0 end) as count2
FROM 
    table1 t1 
    INNER JOIN table2 t2 ON t2.id = t1.rowid

You can also cast boolean type to integer and shorten the sql (true::int = 1, false::int = 0):

 select 
    sum((flag1::int<flag2::int)::int) count1,
    sum((flag1::int>flag2::int)::int) count2    
 from
    table1 t1 
    join table2 t2 ON t2.id = t1.rowid

And because true > false and false < true (at least in PostgreSQL) you can write:

 select 
    sum((flag1 < flag2)::int) count1,
    sum((flag1 > flag2)::int) count2    
 from
    table1 t1 
    join table2 t2 ON t2.id = t1.rowid

Upvotes: 3

Ankit
Ankit

Reputation: 690

Select * from
(
    SELECT COUNT(*) FROM table1 t1 
    INNER JOIN table2 t2 ON t2.id = t1.rowid
    WHERE t1.flag1 = false AND t2.flag2 = true) tab1,
(
    SELECT COUNT(*) FROM table1 t1 
    INNER JOIN table2 t2 ON t2.id = t1.rowid
    WHERE t1.flag1 = true AND t2.flag2 = false) tab2

Upvotes: 0

Tobsey
Tobsey

Reputation: 3400

This should do it for you. I'm not to familiar with PostgreSql but I think it will work.

SELECT 
    SUM(CASE WHEN t1.Flag1 = false AND t2.flag2 = true THEN 1 ELSE 0 END) Count1,
    SUM(CASE WHEN t1.Flag1 = true AND t2.flag2 = false THEN 1 ELSE 0 END) Count2
FROM 
    table1 t1 
    INNER JOIN table2 t2 ON t2.id = t1.rowid

Upvotes: 4

Related Questions