[SQL] select & group by
I've got a problem selecting some specific data from my table. Imagine the following rows: part| mfg | qty | price | eta --- TEST1ABC 10 100(No ETA, as item is in stock) TEST1ABC 12 120 04/04 TEST2CBA 17 10 05/05 TEST2CBA 10 20 (No ETA, as item is in stock) I'd like my selection to produce the following result: part| mfg | qty | qty incoming | highest price | eta - TEST1ABC 10 12 120 04/04 TEST2CBA 10 17 20 05/05 Any clues on how to do this ? I kow the group by part, mfg, max(price) - but I do not know how to deal with the splitting up qty and stock qty and incoming qty. Thanks. /mich ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select & group by
Michael L. Hostbaek wrote: I've got a problem selecting some specific data from my table. Imagine the following rows: part| mfg | qty | price | eta --- TEST1ABC 10 100(No ETA, as item is in stock) TEST1ABC 12 120 04/04 TEST2CBA 17 10 05/05 TEST2CBA 10 20 (No ETA, as item is in stock) I'd like my selection to produce the following result: part| mfg | qty | qty incoming | highest price | eta - TEST1ABC 10 12 120 04/04 TEST2CBA 10 17 20 05/05 Any clues on how to do this ? I kow the group by part, mfg, max(price) - but I do not know how to deal with the splitting up qty and stock qty and incoming qty. How about something like: SELECT aa.part, aa.mfg, aa.qty, bb.qty AS qty_incoming, CASE WHEN aa.price > bb.price THEN aa.price ELSE bb.price END AS highest_price, aa.eta FROM ( SELECT part,mfg,qty,price FROM mytable WHERE eta IS NOT NULL ) aa, ( SELECT part,mfg,qty,price FROM mytable WHERE eta IS NULL ) bb WHERE aa.part = bb.part AND aa.mfg=bb.mfg ; This is assuming you only have one row with "eta" set for each (part,mfg). If not, you'll have to identify which row you want. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] select & group by
On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > I've got a problem selecting some specific data from my table. Imagine > the following rows: > > part | mfg | qty | price | eta > --- > TEST1 ABC 10 100(No ETA, as item is in stock) > TEST1 ABC 12 120 04/04 > TEST2 CBA 17 10 05/05 > TEST2 CBA 10 20 (No ETA, as item is in stock) > > I'd like my selection to produce the following result: > > part | mfg | qty | qty incoming | highest price | eta > - > TEST1 ABC 10 12 120 04/04 > TEST2 CBA 10 17 20 05/05 > > Any clues on how to do this ? I kow the group by part, mfg, max(price) - > but I do not know how to deal with the splitting up qty and stock qty > and incoming qty. use CASE. for example, something like: select part,mfg, sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, sum(CASE WHEN eta is NULL then 0 ELSE qty END) as "qty incoming", max(price) as "highest price", min(eta) as eta group by part,mfg; gnari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] select & group by
On Mon, 2005-04-04 at 10:47 +, Ragnar Hafstað wrote: > On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > > [problem] > [slightly broken solution] I forgot a FROM clause, and you might want to add a ORDER BY clause, if that is important: select part,mfg, sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, sum(CASE WHEN eta is NULL then 0 ELSE qty END) as "qty incoming", max(price) as "highest price", min(eta) as eta from thetable group by part,mfg order by part,mfg; gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] select & group by
Ragnar Hafstað (gnari) writes: > On Mon, 2005-04-04 at 10:47 +, Ragnar Hafstað wrote: > > On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > > > [problem] > > [slightly broken solution] > > I forgot a FROM clause, and you might want to add a > ORDER BY clause, if that is important: > > select part,mfg, >sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, >sum(CASE WHEN eta is NULL then 0 ELSE qty END) as "qty incoming", >max(price) as "highest price", >min(eta) as eta > from thetable > group by part,mfg > order by part,mfg; Thanks, this works brilliantlty ! /mich ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Chield Serial
Hi. Is there a way to make a serial columm that works with father table??? I.e: I have created a table called "vending" and its chield table called "items". The items table have a serial columm, but I need the serial colum starts with 0 for each vending. create table vending ( id serial primary key, --Ok, this serial: 0 - xxx date_ date ); create table items ( vending_id references vending, items_id serial, -- THIS SERIAL NEED to start 0 for each vending_id primary key (vending_id,items_id) ); insert into vending values (1,now()); insert into vending values (2,now()); insert into items values (1); insert into items values (1); insert into items values (2); insert into items values (2); select * from items; vending_id | items_id 1 | 1 1 | 2 2 | 3<<=== Here! The items_id need to be 1 (start again for each vending_id) 2 | 4<<== Thanks all. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Migrated Oracle database to PostgreSQL ??
Hi there, Has anyone migrated Oracle database to PostgreSQL? If yes, could you please help me - 1. How to migrate Procedures and Packages from Oracle to PostgreSQL 2. How to find error messages in PostgreSQL thrown from an Exception (as we can get messages in Oracle with "SQLERRM" keyword) for example -- --Raise an exception RAISE EXCEPTION 'No Data found for record id % ' ,recordid --Insert exception messages into database EXCEPTION IF RAISE_EXCEPTION THEN -- my_insert_function(SQLERRM); Thanks in advance. RegardsDinesh Pandey
Re: [SQL] Migrated Oracle database to PostgreSQL ??
1. How to migrate Procedures and Packages from Oracle to PostgreSQL 2. How to find error messages in PostgreSQL thrown from an Exception (as we can get messages in Oracle with "SQLERRM" keyword) for example -- --Raise an exception RAISE EXCEPTION 'No Data found for record id % ' ,recordid --Insert exception messages into database EXCEPTION IF RAISE_EXCEPTION THEN --my_insert_function(SQLERRM); All your answers can be found here: http://www.postgresql.org/docs/8.0/static/plpgsql.html I can't say if PL/pgSQL is as good as PL/SQL, though. Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Migrated Oracle database to PostgreSQL ??
Yes, but after creating some function, we can get sql error messages. But I not comfortable that much in that. For Procedures and Packages we can use Functions Any other option in pl/PGSQL Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Yasir Malik Sent: Tuesday, April 05, 2005 1:06 AM To: PostgreSQL Subject: Re: [SQL] Migrated Oracle database to PostgreSQL ?? > 1. How to migrate Procedures and Packages from Oracle to PostgreSQL > > > 2. How to find error messages in PostgreSQL thrown from an Exception > (as we can get messages in Oracle with "SQLERRM" keyword) >for example -- > > --Raise an exception >RAISE EXCEPTION 'No Data found for record id % ' ,recordid > > --Insert exception messages into database >EXCEPTION >IF RAISE_EXCEPTION THEN > --my_insert_function(SQLERRM); All your answers can be found here: http://www.postgresql.org/docs/8.0/static/plpgsql.html I can't say if PL/pgSQL is as good as PL/SQL, though. Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
