pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing count on a join, group by required?
7+ messages / 5 participants
[nested] [flat]
* Using count on a join, group by required?
@ 2010-08-11 02:51 Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-08-11 12:39 ` Re: Using count on a join, group by required? Peter Eisentraut <peter_e@gmx.net>
2010-12-14 07:19 ` Re: Using count on a join, group by required? emaratiyya <emaratiyya@hotmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Jose Ildefonso Camargo Tolosa @ 2010-08-11 02:51 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
@ 2010-08-11 12:39 ` Peter Eisentraut <peter_e@gmx.net>
2010-08-11 23:56 ` Re: Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2010-08-11 12:39 UTC (permalink / raw)
To: Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>; +Cc: pgsql-sql
On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote:
> 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?
This is fixed in PostgreSQL 9.1devel (*); there you only need to put the
primary key into the GROUP BY clause. Earlier versions didn't know that
that was enough to ensure a deterministic result.
(*) -- It will probably be a bit over a year before that is released.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-08-11 12:39 ` Re: Using count on a join, group by required? Peter Eisentraut <peter_e@gmx.net>
@ 2010-08-11 23:56 ` Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jose Ildefonso Camargo Tolosa @ 2010-08-11 23:56 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
On Wed, Aug 11, 2010 at 8:09 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote:
>> 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?
>
> This is fixed in PostgreSQL 9.1devel (*); there you only need to put the
> primary key into the GROUP BY clause. Earlier versions didn't know that
> that was enough to ensure a deterministic result.
>
> (*) -- It will probably be a bit over a year before that is released.
Ok, I see, so, in the meantime, just add all columns: no big deal, I
was only curious on why.
Thanks for your answer!
Ildefonso.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
@ 2010-12-14 07:19 ` emaratiyya <emaratiyya@hotmail.com>
2010-12-14 08:53 ` Re: Using count on a join, group by required? Trinath Somanchi <trinath.somanchi@gmail.com>
2010-12-14 16:23 ` Re: Using count on a join, group by required? Rob Sargent <robjsargent@gmail.com>
1 sibling, 2 replies; 7+ messages in thread
From: emaratiyya @ 2010-12-14 07:19 UTC (permalink / raw)
To: pgsql-sql
Hi,Please help me solving this problem. I appreciate..Thankyou.
Create the following table and insert few arbitrary records.
Product (product_id, product_name, supplier_name, quantity, price_per_unit)
You are required to create PL/SQL package that achieves the following
functionalities:
• Obtaining the product supplier name by using the product_id
• Changing the price_per_unit by using the product_id
• When changing the price of the product, you have to have PL/SQL code that
keeps the history of the prices. For this functionality you need to create
audit table: product_audit(product_id, old_price, new_price, date_of_change)
• Updating the quantity by using the product_id
• Get warning when the product level of stock (quantity) goes below certain
threshold (e.g. 10)
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.ht...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-12-14 07:19 ` Re: Using count on a join, group by required? emaratiyya <emaratiyya@hotmail.com>
@ 2010-12-14 08:53 ` Trinath Somanchi <trinath.somanchi@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Trinath Somanchi @ 2010-12-14 08:53 UTC (permalink / raw)
To: emaratiyya <emaratiyya@hotmail.com>; +Cc: pgsql-sql
what are the inputs to the PL-SQL procedure.
On Tue, Dec 14, 2010 at 12:49 PM, emaratiyya <emaratiyya@hotmail.com> wrote:
>
> Hi,Please help me solving this problem. I appreciate..Thankyou.
>
> Create the following table and insert few arbitrary records.
> Product (product_id, product_name, supplier_name, quantity, price_per_unit)
> You are required to create PL/SQL package that achieves the following
> functionalities:
>
> • Obtaining the product supplier name by using the product_id
> • Changing the price_per_unit by using the product_id
> • When changing the price of the product, you have to have PL/SQL
> code that
> keeps the history of the prices. For this functionality you need to create
> audit table: product_audit(product_id, old_price, new_price,
> date_of_change)
> • Updating the quantity by using the product_id
> • Get warning when the product level of stock (quantity) goes below
> certain
> threshold (e.g. 10)
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.ht...
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Regards,
----------------------------------------------
Trinath Somanchi,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-12-14 07:19 ` Re: Using count on a join, group by required? emaratiyya <emaratiyya@hotmail.com>
@ 2010-12-14 16:23 ` Rob Sargent <robjsargent@gmail.com>
2010-12-14 18:14 ` Re: Using count on a join, group by required? Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2010-12-14 16:23 UTC (permalink / raw)
To: emaratiyya <emaratiyya@hotmail.com>; +Cc: pgsql-sql
Shouldn't you be doing your own homework?
emaratiyya wrote:
> Hi,Please help me solving this problem. I appreciate..Thankyou.
>
> Create the following table and insert few arbitrary records.
> Product (product_id, product_name, supplier_name, quantity, price_per_unit)
> You are required to create PL/SQL package that achieves the following
> functionalities:
>
> • Obtaining the product supplier name by using the product_id
> • Changing the price_per_unit by using the product_id
> • When changing the price of the product, you have to have PL/SQL code that
> keeps the history of the prices. For this functionality you need to create
> audit table: product_audit(product_id, old_price, new_price, date_of_change)
> • Updating the quantity by using the product_id
> • Get warning when the product level of stock (quantity) goes below certain
> threshold (e.g. 10)
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using count on a join, group by required?
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-12-14 07:19 ` Re: Using count on a join, group by required? emaratiyya <emaratiyya@hotmail.com>
2010-12-14 16:23 ` Re: Using count on a join, group by required? Rob Sargent <robjsargent@gmail.com>
@ 2010-12-14 18:14 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2010-12-14 18:14 UTC (permalink / raw)
To: emaratiyya <emaratiyya@hotmail.com>; +Cc: pgsql-sql
If you showed your work, you might get decent hints if not solutions.
On 12/14/2010 09:23 AM, Rob Sargent wrote:
> Shouldn't you be doing your own homework?
>
> emaratiyya wrote:
>> Hi,Please help me solving this problem. I appreciate..Thankyou.
>>
>> Create the following table and insert few arbitrary records. Product
>> (product_id, product_name, supplier_name, quantity, price_per_unit)
>> You are required to create PL/SQL package that achieves the following
>> functionalities:
>>
>> • Obtaining the product supplier name by using the product_id
>> • Changing the price_per_unit by using the product_id
>> • When changing the price of the product, you have to have PL/SQL
>> code that
>> keeps the history of the prices. For this functionality you need to
>> create
>> audit table: product_audit(product_id, old_price, new_price,
>> date_of_change)
>> • Updating the quantity by using the product_id
>> • Get warning when the product level of stock (quantity) goes below
>> certain
>> threshold (e.g. 10)
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-12-14 18:14 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-11 02:51 Using count on a join, group by required? Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-08-11 12:39 ` Peter Eisentraut <peter_e@gmx.net>
2010-08-11 23:56 ` Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
2010-12-14 07:19 ` emaratiyya <emaratiyya@hotmail.com>
2010-12-14 08:53 ` Trinath Somanchi <trinath.somanchi@gmail.com>
2010-12-14 16:23 ` Rob Sargent <robjsargent@gmail.com>
2010-12-14 18:14 ` Rob Sargent <robjsargent@gmail.com>
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