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.


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

How can I get just the latest row? (6h)

To find last next value on the basis of Current and next row values in Sql server 2008 (13h)

Migrate 2008R2 FCI to 2016 AOAG (23h)

Parse the values into multiple columns (23h)

SQL 2017 MLS - cant add python packages (1d)

Number weeks from April to September for every year (2d)

Today date -1 year (2d)

To get Total Hour & Late Hour from Time In, Time Out, 1st Break & 2nd Break (3d)

- Advertisement -