Skip site navigation (1) Skip section navigation (2)

Re: Using count on a join, group by required?

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Jose Ildefonso Camargo Tolosa <ildefonso(dot)camargo(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Using count on a join, group by required?
Date: 2010-08-11 12:39:28
Message-ID: 1281530368.8340.4.camel@fsopti579.F-Secure.com (view raw or flat)
Thread:
Lists: pgsql-sql
On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote:
> And it works, it gives me something like:
> 
>  product_id |         name         |     code      | manufacturer_id |
>     manufacturer_name      | num_serials
> ------------+----------------------+---------------+-----------------+----------------------------+-------------
>          17 | THE product          | 1235711131719 |              19 |
> THE product's manufacturer |           5
>           6 | Car Battery 500A 12V | 7591512021575 |               8 |
> Acumuladores Duncan, C.A.  |          11
>           1 | Test product 1       | 123456789012  |               1 |
> Test Manufacturer          |           6
> 
> Which is correct, and exactly what I wanted.
> 
> So far, so good.  The thing is: the group by clause, I had to add it
> because the parser forced me to, because it complained like this:
> 
> ERROR:  column "manufacturer.name" must appear in the GROUP BY clause
> or be used in an aggregate function
> 
> and I had to include *all* the requested columns on the group by
> clause, can anybody tell me why? or at least point to some doc that
> help me understanding this?

This is fixed in PostgreSQL 9.1devel (*); there you only need to put the
primary key into the GROUP BY clause.  Earlier versions didn't know that
that was enough to ensure a deterministic result.

(*) -- It will probably be a bit over a year before that is released.


In response to

Responses

pgsql-sql by date

Next:From: Jose Ildefonso Camargo TolosaDate: 2010-08-11 23:56:27
Subject: Re: Using count on a join, group by required?
Previous:From: A. KretschmerDate: 2010-08-11 04:50:07
Subject: Re: Duplicate rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group