Combine 2 accounts with different ones on the same table

Question

Is there a better way to do 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

What about

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

It is not necessary to be better, but it is a useful idiom in your mental arsenal!

Answer 2

I voted to use two different queries for simplicity and improved code readability. There are not many advantages of using smart hack to combine queries, when you can achieve the same result and more readable code with two queries,

Answer 3

Here's another way:

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

Answer 4

This gives you a different result, 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)