SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

Anonymous
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...

users
user_id
username

board
user_id
club_id
post

members
user_id
club_id

clubs
club_id
club_name

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(b.post) 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

rob_j_egan
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

rob_j_egan
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 :)

Rob

Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000