Combine 2 counts with different where on the same table

Question

Is there a better way of doing this ?

SELECT
  (SELECT count(*) FROM `tbl` WHERE `status` = 0) as 'text1',
  (SELECT count(*) FROM `tbl` WHERE `status` > 0) as 'text2'

text1 and text2 are headers.

Answer 1

How about

select sum(if(status=0,1,0)) as zeros, 
       sum(if(status>0,1,0)) as greater 
from tbl;

Might not necessarily be better, but it's a useful idiom to have in your mental arsenal!

Answer 2

This gives you a different output, but sorta works:

SELECT `status` > 0 AS 'stat', COUNT( * )
FROM `tbl`
GROUP BY stat

Output:

stat | COUNT(*)
-------------------------------
 0   | (count where status = 0)
 1   | (count where status > 0)

Answer 3

I vote for using two different queries for the sake of simplicity and improved code readability. There isn't much benefit of using a clever hack to combine the queries, when you can achieve the same result and more readable code by having two queries,

Answer 4

Here's another way:

SELECT
  COUNT(NULLIF(`status` = 0, 0)),
  COUNT(NULLIF(`status` > 0, 0))
FROM `tbl`