Re: [SQL] Using count on a join, group by required?
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) >> >> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using count on a join, group by required?
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) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using count on a join, group by required?
what are the inputs to the PL-SQL procedure. On Tue, Dec 14, 2010 at 12:49 PM, 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) > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.html > 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,
Re: [SQL] Using count on a join, group by required?
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.html 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
Re: [SQL] Using count on a join, group by required?
On Wed, Aug 11, 2010 at 8:09 AM, Peter Eisentraut 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using count on a join, group by required?
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
