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.
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
And yes we can also use COUNT
like this:
SELECT
COUNT(CASE WHEN Result='fail' THEN 1 ELSE NULL END) ,
COUNT(*) FROM ...
See this SQLFiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With