Filtered count and total count without subquery

count, mysql, sql

Solution

ANSI SQL

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

SQLFiddle Demo

MySQL Specific (as pointed by eggyal)

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

SQLFiddle Demo

Problem

Suppose I have a table like this: ``` ID Result ------------- 1  ,  'pass' 2  ,  'pass' 3  ,  'fail' 4  ,  'fail' 5  ,  'fail' 6 ,   'fail' ``` Is there any simple way to find `COUNT WHERE result = 'fail' AND total COUNT`. Expected output: ``` FailCount TotalCount ----------------------- 4 6 ``` Yes, we can do this using subquery like this: ``` SELECT (SELECT COUNT(result) FROM t WHERE result='fail') AS FAILCount , COUNT(result) AS TotalCount FROM t; ``` But is there any way to do like this: ``` SELECT COUNT(WHERE Result='fail') , COUNT(Result) FROM ... ``` I am trying in this fiddle.

Original source