im sure this is easy but im having a block I am trying to write some sql against a single table that has (simplified for example)
RunName, Result
foo, pass
foo, pass
foo, fail
foo, pass
boo, pass
boo, fail
boo, fail
soo, pass
I was a query that will return a count of pass or fail for each name
something like for fail
foo, 1
boo, 2
soo, 0
and for pass
foo, 3
boo, 1
soo, 1
Normally you'd do a simple COUNT and GROUP BY RunName, but that will not show "zero results" like soo's fails.
This should work even for zero results (on MySQL)
SELECT RunName, SUM(Result='fail')
FROM TableA
GROUP BY RunName;
Demo here.
Edit: As Aaron points out, that only works on MySQL, this works on SQL Server also;
SELECT RunName, SUM(CASE WHEN Result = 'fail' THEN 1 ELSE 0 END) as fails
FROM TableA
GROUP BY RunName
Demo here.
Edit2: As Marcus points out, it's may not be a very index friendly way of doing the query, so if you have a primary key you may be better off doing a self join with a count/group to get the correct result;
SELECT a.RunName, COUNT(b.Result)
FROM TableA a LEFT JOIN TableA b ON a.id=b.id AND b.Result='fail'
GROUP BY RunName
Demo here.
SQL Server. SQL is for generic SQL.This is for all the fail results, just change the where clause for pass:
select RunName, count(Result)
from [tableName]
where Result = 'fail'
group by RunName
You need to use a group by.
Something like this
Select count(result), runname, result from sometable
group by runname, result
this should give you output like
count runname result
2 foo pass
3 foo fail
Is this what you want?
SELECT RunName, SUM(CASE WHEN Result = 'pass' THEN 1 ELSE 0 END) as NumPass,
SUM(CASE WHEN Result = 'fail' THEN 1 ELSE 0 END) as NumFail
FROM yourtable
GROUP BY RunName
If you want separate results for each then you can just do a filter on the where clause like so:
SELECT RunName, COUNT(1) as NumPass
FROM yourtable
WHERE Result = 'pass'
GROUP BY RunName
SELECT RunName, COUNT(1) as NumFail
FROM yourtable
WHERE Result = 'fail'
GROUP BY RunName
Try this for fail
select RunName, count(Result) as cnt
from your_table
where Result = 'fail'
group by RunName
and this for pass
select RunName, count(Result) as cnt
from your_table
where Result = 'pass'
group by RunName
Try this:
For Pass:
SELECT RunName, COUNT(*)
FROM TableName
GROUP BY Result
WHERE UPPER(Result) = 'PASS'
For Fail:
SELECT RunName, COUNT(*)
FROM TableName
GROUP BY Result
WHERE UPPER(Result) = 'FAIL'
UPPER() isn't necessary here. It causes an extra filesort for the GROUP BY, rather than using an index on Result.
GROUP BY and WHERE Result = 'pass'. I wouldn't assume, given the data provided by the OP, that data will include 'PASS' and 'pass'. I was offering criticism on using a function in GROUP BY and didn't even notice you don't have a WHERE clause. -2 for scanning all rows.WHERE clause, you're still scanning all rows unless you remove the UPPER() function, but at least you tried.