If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
ERROR: aggregate function?
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 |
#3
|
|||
|
|||
ERROR: aggregate function?
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 |
#4
|
|||
|
|||
ERROR: aggregate function?
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 |
#5
|
|||
|
|||
ERROR: aggregate function?
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 |
Thread Tools | |
Display Modes | |
|
|