ERROR: aggregate function?

Hi, I wonder if someone could help. I'm trying to run the following query on
an Access database..

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)

The Stories table contains multiple rows, and the comments table may or may
not contain rows which relate to the story (stories.storyid =
comments.storyid).

I'm trying to obtain 1 story row which includes a count of the number of
related Comments records, like...

StoryTitle | ComCount
-------------------------------------------
The Jungle Book | 3

But everytime I run the query I get the following error...

SQL Execution Error.
Error Source: Microsoft JET Database Engine
Error Message: You tried to execute a query that does not include the
specified expression 'StoryTitle' as part of an aggregate function.

What does this error mean, and how can I fix my SQL Statement to make it work?

Thanks



Dan

Fix the query.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

When you use an aggregate (totals) query, you must include all the fields
that are in the select clause in a group by clause IF they are not being
aggregated (Counted, Summed, Averaged, etc).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi, I wonder if someone could help. I'm trying to run the following query
on
an Access database..

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)

The Stories table contains multiple rows, and the comments table may or
may
not contain rows which relate to the story (stories.storyid =
comments.storyid).

I'm trying to obtain 1 story row which includes a count of the number of
related Comments records, like...

StoryTitle | ComCount
-------------------------------------------
The Jungle Book | 3

But everytime I run the query I get the following error...

SQL Execution Error.
Error Source: Microsoft JET Database Engine
Error Message: You tried to execute a query that does not include the
specified expression 'StoryTitle' as part of an aggregate function.

What does this error mean, and how can I fix my SQL Statement to make it
work?

Thanks



Dan

Hi John,

Thanks for the response, that works. Well, the query runs...

However, I get 0 records returned for the queries, presumably because there
are no comments related to it?

How can I get the query to return the StoryTitle with a ComCount of 0
(rather than just not displaying the story) ?

Thanks, Dan.


"John Spencer" wrote:

Fix the query.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

When you use an aggregate (totals) query, you must include all the fields
that are in the select clause in a group by clause IF they are not being
aggregated (Counted, Summed, Averaged, etc).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi, I wonder if someone could help. I'm trying to run the following query
on
an Access database..

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)

The Stories table contains multiple rows, and the comments table may or
may
not contain rows which relate to the story (stories.storyid =
comments.storyid).

I'm trying to obtain 1 story row which includes a count of the number of
related Comments records, like...

StoryTitle | ComCount
-------------------------------------------
The Jungle Book | 3

But everytime I run the query I get the following error...

SQL Execution Error.
Error Source: Microsoft JET Database Engine
Error Message: You tried to execute a query that does not include the
specified expression 'StoryTitle' as part of an aggregate function.

What does this error mean, and how can I fix my SQL Statement to make it
work?

Thanks



Dan

Change the join type.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories LEFT JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi John,

Thanks for the response, that works. Well, the query runs...

However, I get 0 records returned for the queries, presumably because
there
are no comments related to it?

How can I get the query to return the StoryTitle with a ComCount of 0
(rather than just not displaying the story) ?

Thanks, Dan.


"John Spencer" wrote:

Fix the query.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

When you use an aggregate (totals) query, you must include all the fields
that are in the select clause in a group by clause IF they are not being
aggregated (Counted, Summed, Averaged, etc).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi, I wonder if someone could help. I'm trying to run the following
query
on
an Access database..

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)

The Stories table contains multiple rows, and the comments table may or
may
not contain rows which relate to the story (stories.storyid =
comments.storyid).

I'm trying to obtain 1 story row which includes a count of the number
of
related Comments records, like...

StoryTitle | ComCount
-------------------------------------------
The Jungle Book | 3

But everytime I run the query I get the following error...

SQL Execution Error.
Error Source: Microsoft JET Database Engine
Error Message: You tried to execute a query that does not include the
specified expression 'StoryTitle' as part of an aggregate function.

What does this error mean, and how can I fix my SQL Statement to make
it
work?

Thanks



Dan

Thanks John... worked great!



"John Spencer" wrote:

Change the join type.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories LEFT JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi John,

Thanks for the response, that works. Well, the query runs...

However, I get 0 records returned for the queries, presumably because
there
are no comments related to it?

How can I get the query to return the StoryTitle with a ComCount of 0
(rather than just not displaying the story) ?

Thanks, Dan.


"John Spencer" wrote:

Fix the query.

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE Stories.StoryID = [?]
GROUP BY Stories.StoryTitle

When you use an aggregate (totals) query, you must include all the fields
that are in the select clause in a group by clause IF they are not being
aggregated (Counted, Summed, Averaged, etc).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"musosdev" wrote in message
...
Hi, I wonder if someone could help. I'm trying to run the following
query
on
an Access database..

SELECT Stories.StoryTitle, COUNT(Comments.CommentID) AS ComCount
FROM (Stories INNER JOIN
Comments ON Stories.StoryID = Comments.StoryID)
WHERE (Stories.StoryID = ?)

The Stories table contains multiple rows, and the comments table may or
may
not contain rows which relate to the story (stories.storyid =
comments.storyid).

I'm trying to obtain 1 story row which includes a count of the number
of
related Comments records, like...

StoryTitle | ComCount
-------------------------------------------
The Jungle Book | 3

But everytime I run the query I get the following error...

SQL Execution Error.
Error Source: Microsoft JET Database Engine
Error Message: You tried to execute a query that does not include the
specified expression 'StoryTitle' as part of an aggregate function.

What does this error mean, and how can I fix my SQL Statement to make
it
work?

Thanks



Dan