Multiple Select Count(*) - newbie question

  • Thread starter Thread starter Saber
  • Start date Start date
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
 
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
 
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 :)
 
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.
 
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...
 
Back
Top