Using count on a join, group by required?
От | Jose Ildefonso Camargo Tolosa |
---|---|
Тема | Using count on a join, group by required? |
Дата | |
Msg-id | AANLkTinYNWi1AQft188MBWj4n6OBfSQX1DdAO2Jf8wQu@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Using count on a join, group by required?
Re: Using count on a join, group by required? |
Список | 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
В списке pgsql-sql по дате отправления: