Multiple Select Count(*) - newbie question

S

Saber

How can I have a record with 3 fields and each field is a Count(*), I mean
how can I combine three queries below into one query?

SELECT Count(id) as PersonalCount from tblPosts where posttopic='personal';
SELECT Count(id) as AspCount from tblPosts where posttopic='asp';
SELECT Count(id) as TechnologyCount from tblPosts where
posttopic='technology';


TIA
 
R

Rick Brandt

Saber said:
How can I have a record with 3 fields and each field is a Count(*), I mean
how can I combine three queries below into one query?

SELECT Count(id) as PersonalCount from tblPosts where posttopic='personal';
SELECT Count(id) as AspCount from tblPosts where posttopic='asp';
SELECT Count(id) as TechnologyCount from tblPosts where
posttopic='technology';
Click to expand...

SELECT
SUM(IIf(posttopic="personal",1,0)) AS PersonalCount,
SUM(IIf(posttopic="asp",1,0)) AS AspCount,
SUM(IIf(posttopic="technology",1,0)) AS TechnologyCount,
FROM tblPosts
 
S

Saber

SELECT
SUM(IIf(posttopic="personal",1,0)) AS PersonalCount,
SUM(IIf(posttopic="asp",1,0)) AS AspCount,
SUM(IIf(posttopic="technology",1,0)) AS TechnologyCount,
FROM tblPosts
Click to expand...


Thanks, nice idea :)
 
S

Saber

I got an error and couldn't find out why.
it says:
The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.
 
K

Ken Snell [MVP]

Rick inadverstently left an extra comma in the SQL statement at the end of
SUM(IIf(posttopic="technology",1,0)) AS TechnologyCount,

Try this:

SELECT
SUM(IIf(posttopic="personal",1,0)) AS PersonalCount,
SUM(IIf(posttopic="asp",1,0)) AS AspCount,
SUM(IIf(posttopic="technology",1,0)) AS TechnologyCount
FROM tblPosts
--

Ken Snell
<MS ACCESS MVP>

Saber said:
I got an error and couldn't find out why.
it says:
The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.
Click to expand...
 
Top