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
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 |