field not part of aggregate function

[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;

 

[Jerry Whittle]

qYr2CommentsSummary_A_Union.CNTYNAME needs to be in both the SELECT and GROUP
BY clauses.

 

[Bob Barrows]

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.

 

[jmoore]

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.

 

[jmoore]

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.

 

[Bob Barrows]

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

 

[Jerry Whittle]

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.