Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Multiple record counts in one SQL statement
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 08/05/2000 :  11:16:49  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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."

Article Link.

Starting Member

0 Posts

Posted - 03/19/2001 :  19:21:30  Show Profile  Reply with Quote
Two Different Kinds of Counting

This question reminds me...
Here's a stripped down version of a database...





Now I want to create a recordset that makes summary page for a particular user, by counting up the number of post's in the BOARD table, and the number of user_id's in the MEMBERS table for each club that the user is a member of. So you want:

SELECT count( AS post_count, and count(m.user_id) AS members_count
FROM board AS b, members AS m
WHERE b.club_id = m.club_id AND b.user_id = ?
GROUP BY club_id

However... this does not give you the correct counts... so how would you write the query to count both? can you? I currently do the count of members in a seperate query within my ASP page, which works, but perhaps uses more resources.

Go to Top of Page

Starting Member

4 Posts

Posted - 02/18/2002 :  11:24:16  Show Profile  Reply with Quote
"SELECT estCounty=(SELECT Count(*) FROM est WHERE County = 'Lancashire'), estCountry=(SELECT Count(*) FROM est WHERE Country= 'England')"

Produces an error. Can anyone see what it is

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error.
/dev/index5.asp, line 9

cheers Rob :)

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 02/18/2002 :  11:47:21  Show Profile  Reply with Quote
Access (well, Jet probably) doesn't seem to like things of that ilk.
Even SELECT (SELECT COUNT(*) FROM tbl) from inside Access gives me a strange "Reserved error (-3025); there is no message for this error." unmessage.

Go to Top of Page

Starting Member

4 Posts

Posted - 02/18/2002 :  12:06:23  Show Profile  Reply with Quote
Looks like a move upto SQL 7.0 is then in order.

Thanks, i maybe back for more advice :)


Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 02/18/2002 :  13:08:29  Show Profile  Visit robvolk's Homepage  Reply with Quote
This should work in Access:

SELECT Sum(IIf(County='Lancashire', 1, 0)) AS estCounty,
Sum(IIf(Country='England', 1, 0)) AS estCountry FROM est

I'm not sure if your original WHERE clause is needed, but you can always add it.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000