Selecting distinct rows and the count of their occurence in one query.

[Sepehre]

Hi,

I have two related tables setup like this:

run_results:
run_id - Primary Key - Int
run_date - - Date

test_results:
run_id - Multiple Key - Int
test_name - - varchar
errors - - Int

Every run_results.run_id corresponds to one or more test_results.run_id.
What I would like to search for, is the test_result.test_name's which have had errors>0 for some run_results.run_id AND the number of times they have occured.

The trouble is I can't do them both at once.

I know I can do:
SELECT DISTINCT test_name FROM test_results WHERE errors>0;

And also,
SELECT COUNT(*) FROM test_results WHERE test_name = 'test1 or test2...'

but I would like the second query to be automatically executed for all the results from the first query, giving a result such as this:

| test_name | number of times it failed |
+-----------+------------------------+
| test1 | 20 |
| test2 | 1 |

Any ideas or suggestions would be appreciated! =)

- Sepehr

[NogDog]

If I understand correctly, I think you could do:

SELECT test_name, COUNT(*) FROM test_results WHERE errors > 0 GROUP BY test_name;

(I'm not 100% positive that's the correct, though.)

[Sepehre]

thanks for your reply!

For some reason I had never used Group By before...


SELECT test_name, COUNT(*) AS failure_count FROM test_results WHERE errors > 0 GROUP BY test_name ORDER BY failure_count;