field not part of aggregate function

  • Thread starter Thread starter jmoore
  • Start date Start date
J

jmoore

The query below works fine, except I need to use it for a subreport and link
it by qYr2CommentsSummary_A_Union.CNTYNAME. When I add this field to the
select statement and try to open in datasheet view I get an error because
CNTYNAME is not part of an aggregate function. Can you please explain how to
correct this. Thanks.

Select qYr2CommentsSummary_A_Union.Question,
qYr2CommentsSummary_A_Union.Answer, Count
(qYr2CommentsSummary_A_Union.Answer) AS CountofComments
FROM qYr2CommentsSummary_A_Union
GROUP BY qYr2CommentsSummary_A_Union.Question,
qYr2CommentsSummary_A_Union.Answer;
 
qYr2CommentsSummary_A_Union.CNTYNAME needs to be in both the SELECT and GROUP
BY clauses.
 
Either add it to the GROUP BY clause, or aggregate it, whichever is
appropriate. If it isn't really a "key" for the grouping, then I would
choose to aggregate it.
The aggregation functions include SUM, COUNT, Min, Max, First, Last,
etc.
If you chose to aggregate it, Max, Min, First or Last would be
applicable.
 
Oh, such an easy solution. I thought I couldn't include it in the group by
clause because I did not want it in the final results. Thanks very much.
 
I am new at SQL. I added the field to the group by clause and was able to
have the subreport display as desired. Do you mean I can have 2 aggregate
statements in the same query (e.g., COUNT and First)? How does that work?
Thanks for the help.
 
Sure, you just do it <scratches head> ...

select grouping_field, sum(numeric_field) as Total, First(text_field) as
Description, Count(text_field) as HowMany from table group by
grouping_field
 
More than 2 aggrregate clause actually. For example you could take a table of
sales and group by year and month then show the Min sale, Max sale, Avg of
sales, and Sum of sales all in the same query.
 
Back
Top