Re: Group By Statement - how to display fields not in the statement?

On Fri, Aug 21, 2009 at 04:50:52PM -0700, lcp_ wrote:
>
>
> I have the following query:
>
> SELECT count(*), address, organizati FROM afterschool
> WHERE verified IS TRUE
> Group BY address, organizati HAVING count(*) >1
>
> I would like to have the individual records from this query display with
> other fields not in the Group By clause. If I try to add other fields to the
> select statement I get this error:
>
> ERROR: column "afterschool.firstname" must appear in the GROUP BY clause or
> be used in an aggregate function
>
> Any ideas on how I can both Group By these fields and limit to counts of
> more than one, and also display the records with more than just the fields I
> am grouping by?

In any given query, if a field is not part of the GROUP BY clause, it needs to
be derived from an aggregate function, such as count(), sum(), etc. One thing
you might do is join the results of the query you already have with the
afterschool table again, like this:

SELECT a.count, b.* FROM (
SELECT count(*), address, organizati
FROM afterschool WHERE verified
GROUP BY address, organizati
HAVING count(*) > 1
) a
JOIN afterschool b USING (address, organizati);

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com