Multiple record counts in one SQL statement

By Bill Graziano on 14 August 2000 | 5 Comments | 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.

Discuss this article: 5 Comments so far. Print this Article. This page has been read 62,776 times.

If you like this article you can sign up for our newsletter. We send it out each week that we post a new article. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Email This • Subscribe to this feed • Kick it • Save to del.icio.us • View blog reactions

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)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email:

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

- Sponsor's Message -

SQLShare.com Videos

Writing to the Registry from TSQL Using XP_Instance_RegWrite

Reading from the registry to figure out a configuration issue or two is common, but writing to the registry isn't something you'll need to do very often. Still, it's a useful trick to know and we've put it to work showing you how to alter the default folder used for database backups.

Using XP_Instance_RegRead To Get Default Database Path

SQL saves more than a few things to the registry and we can leverage that by reading those settings from TSQL using xp_instance_regread. Easy to use and only one minor quirk to learn, and you'll reading from the registry. Look for the follow up video that demonstrates how to write changes to the registry too.

How to Change the Default Path for New Databases

Typing CREATE DATABASE MyDB gets the job done, but it puts the files in whatever location has been set as the default - which means you should make sure the default is set to a good place, or specify them as part of the CREATE. We'll show you how to adjust the setting (and we've got a video coming up that shows you how to change it from code too!).

Overview of Data Driven Subscriptions in Reporting Services

Subscriptions are a powerful feature of Reporting Services because they allow users to decide which reports to receive. But what do you do when the boss wants a report sent to a group of users, perhaps based on some additional criteria that frequently changes? That's where data driven subscriptions make sense and in our lesson today Devin does a nice overview of the options and requirements.

Using DatePart and DateName

Ever want to get the month out of a date as a number, or as literal text? Many people will do it by parsing the date as a string, but we've got some built in functions that will do it cleanly and consistently.