CODEWITHSUNDEEP
mysqlsql
ducati1212
ducati1212

Reputation: 855

sql query with a count

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

Upvotes: 0

Views: 163

Answers (6)

Mark Kram
Mark Kram

Reputation: 5832

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'

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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.

Upvotes: 4

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

aweis
aweis

Reputation: 5596

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

Upvotes: 2

clyc
clyc

Reputation: 2450

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

Upvotes: 1

Sean Barlow
Sean Barlow

Reputation: 588

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

Upvotes: 1

Related Questions