| 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: | Whole Thread | Raw Message | 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
| 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 |