From: Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Using count on a join, group by required?
Date: Tue, 10 Aug 2010 22:21:13 -0430
Message-ID: <AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ildefonso.camargo@gmail.com
Subject: Re: Using count on a join, group by required?
In-Reply-To: <AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox