Thread: Using count on a join, group by required?
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
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.
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.
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.
what are the inputs to the PL-SQL procedure.<br /><br /><div class="gmail_quote">On Tue, Dec 14, 2010 at 12:49 PM, emaratiyya<span dir="ltr"><<a href="mailto:emaratiyya@hotmail.com">emaratiyya@hotmail.com</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left:1ex;"><br /> Hi,Please help me solving this problem. I appreciate..Thankyou.<br /><br /> Create the followingtable and insert few arbitrary records.<br /> Product (product_id, product_name, supplier_name, quantity, price_per_unit)<br/> You are required to create PL/SQL package that achieves the following<br /> functionalities:<br /><br/> • Obtaining the product supplier name by using the product_id<br /> • Changing the price_per_unit byusing the product_id<br /> • When changing the price of the product, you have to have PL/SQL code that<br /> keepsthe history of the prices. For this functionality you need to create<br /> audit table: product_audit(product_id, old_price,new_price, date_of_change)<br /> • Updating the quantity by using the product_id<br /> • Get warningwhen the product level of stock (quantity) goes below certain<br /> threshold (e.g. 10)<br /><br /> --<br /> Viewthis message in context: <a href="http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.html" target="_blank">http://postgresql.1045698.n5.nabble.com/Using-count-on-a-join-group-by-required-tp2471469p3304147.html</a><br />Sent from the PostgreSQL - sql mailing list archive at Nabble.com.<br /><font color="#888888"><br /> --<br /> Sent viapgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all" /><br/>-- <br />Regards,<br />----------------------------------------------<br />Trinath Somanchi,<br />
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) > >
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) >> >>