DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=U/1F9fbyOvkWnMGiFRadirOUghV9WAImrcizf0YkXdcaaz7Sxks0LvksuAVi/whj6I
	K13/RetdSJ9m34YFNnHvhm9INzfbVwVFmL0Kw1ZDlUxBrnRiTL4suPGaoWqjVy87peqS
	RCGca2IR+7KsW+4N6zfVtwfwCZkW0Hj8RDg7I=
MIME-Version: 1.0
Date: Tue, 10 Aug 2010 22:21:13 -0430
Message-ID: <AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com>
Subject: Using count on a join, group by required?
From: Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

Greetings!

First: This is working, I just need a clarification on concept, so, it
is not necessary for you to look deeply at the SQL statement.

I have this:

Table: products that references manufacturer via
products.manufacturer_id to manufacturer.id (not important, just
informative).
Table: product_serials that references products via
product_serials.product_id to products.id

And I wanted to get this output:

Product ID, Product Name, Product Code, Manufacturer ID, Manufacturer
Name, Number of Associated Serials.

So, I build a query for that:

select products.id as product_id,products.name as name,products.code
as code,manufacturer.id as manufacturer_id,manufacturer.name as
manufacturer_name,count(product_serials.product_id) as num_serials
from products left join manufacturer on
products.manufacturer_id=manufacturer.id left join product_serials on
product_serials.product_id=products.id group by
products.id,products.name,products.code,manufacturer.id,manufacturer.name;

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?

Thanks in advance,

Ildefonso Camargo