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

Update error. Subquery returned more than 1 value (4d)

Resource Governor Opinions (5d)

Order by string has dash (5d)

SQL Server to PostgreSQL (5d)

SQL Union Create Custom Field to Different 2 Tables Output (5d)

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (5d)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (5d)

Filter query base on date field (10d)

- Advertisement -