Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-08-05 : 11:16:49
|
| 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. |
|
|
rob_j_egan
Starting Member
4 Posts |
Posted - 2002-02-18 : 11:24:16
|
| "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 isMicrosoft 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 :) |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-02-18 : 11:47:21
|
| 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. |
|
|
|
rob_j_egan
Starting Member
4 Posts |
Posted - 2002-02-18 : 12:06:23
|
| Looks like a move upto SQL 7.0 is then in order.Thanks, i maybe back for more advice :)Rob |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-18 : 13:08:29
|
| This should work in Access:SELECT Sum(IIf(County='Lancashire', 1, 0)) AS estCounty, Sum(IIf(Country='England', 1, 0)) AS estCountry FROM estI'm not sure if your original WHERE clause is needed, but you can always add it. |
|
|
|
|
|
|
|
|
Article: Multiple record counts in one SQL statement