Multiple Select Count(*) - newbie question

[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

 

[Rick Brandt]

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';

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

 

[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

Thanks, nice idea

 

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

 

[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>

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.