Filtered Quantity and Total without Subquery

Question

Suppose I have a table like this:

ID     Result
-------------
 1  ,  'pass'
 2  ,  'pass'
 3  ,  'fail'
 4  ,  'fail'
 5  ,  'fail'
 6 ,   'fail'

Is there an easy way to find COUNT WHERE result = 'fail' AND total COUNT.

Expected Result:

FailCount    TotalCount
-----------------------
    4            6

Yes, we can do this using a subquery like this:

SELECT 
(SELECT COUNT(result) FROM t WHERE result='fail') AS FAILCount
, COUNT(result)
AS TotalCount FROM t;

But is there a way to do this:

SELECT COUNT(WHERE Result='fail') , COUNT(Result) FROM ...

+5

hims056 Aug 28 '12 at 12:21

3 answers

And yes, we can also use it like this:

SELECT 
   COUNT(CASE WHEN Result='fail' THEN 1 ELSE NULL END) , 
   COUNT(*) FROM ...

+4

hims056 Aug 28 '12 at 12:36

SELECT 
   SUM(CASE WHEN Result='fail' THEN 1 ELSE 0 END) as FailCount, 
   COUNT(Result) as TotalCount FROM table_name

0

Hiren soni Aug 28 '12 at 12:25

Raphaël althaus · Accepted Answer · 2012-08-28T12:22:34+0000

ANSI SQL

SELECT 
   SUM(CASE WHEN Result='fail' THEN 1 ELSE 0 END) , 
   COUNT(*) FROM ...

MySQL specification (as indicated by eggyal)

SELECT 
       SUM(Result='fail') , 
       COUNT(*) FROM ...