ERROR: aggregate function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
..
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks John... worked great!



John Spencer said:
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 said:
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 said:
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
..

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

can't use an aggregate function in an expression? 4
Opinion about aggregates 8
aggregate function error 1
aggregate function error 7
Aggregate function 2
Aggregate function 4
Aggregate function errors 6
Aggregate calculations on calculated query fields? 6

Back
Top