Using count on a join, group by required?

From: Jose Ildefonso Camargo Tolosa <ildefonso(dot)camargo(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Using count on a join, group by required?
Date: 2010-08-11 02:51:13
Message-ID: AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2010-08-11 04:50:07 Re: Duplicate rows
Previous Message Edward W. Rouse 2010-08-10 21:19:35 Re: Duplicate rows