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:

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

Multiple joins vs. 1 lookup table (alternatives) (8h)

NULL IN List (2d)

When Dynamic Connection Fails, Package Succeeds but Job Fails (2d)

Backup failure with Linked Server to another MS SQL unavailable (2d)

Need Help to transpose the result in SQL (2d)

Views in restored MS Dynamics database return zero lines (2d)

DROP Table #TempTable (2d)

Unpivot (2d)

- Advertisement -