Multiple record counts in one SQL statement

By Bill Graziano on 14 August 2000 | Tags: SELECT

Martin writes "Hi, I was wondering if it is possible to have more that one count function in one sql statement. Say something like: I would like to return how many times "reference" eq 1 and how many times "refenrece" eq 2, so i get two fields back, one counting the number of records where "reference" eq 1 and the other counting the number of records where "reference" eq 2. Thanks."

It certainly is possible. If you want to know how many times a given field contains a certain value you can use a GROUP BY clause. The code might look something like this:

SELECT Reference, Record_Count=Count(*)
FROM Table1
GROUP BY Reference

This will return one row per value with a count of how many times that value occured. You could use a WHERE clause to only display certain records. This does create a record set for you to loop through though. If you truly want one record with two values you can code something like this:

SELECT
Count_1=(SELECT Count(*) FROM Table1 WHERE Reference = 1),
Count_2=(SELECT Count(*) FROM Table1 WHERE Reference = 2)

Note that the outside SELECT has no table in it. It is simpy used to return the results from the two subqueries. In this scenario it is also possible to SELECT from different tables or use different fields to generate results. This solution works better the more complex the WHERE clauses are.