[SQL] Group by and aggregates
List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int Here a small sample of contents in my table: some_id partno status cmupqty 1 test1 stock 10.00 15 2 test2 incoming12.00 10 3 test1 incoming15.00 60 4 test1 incoming14.00 11 My SQL select statement will then group together partno, status and aggregate sum(qty) and max(cmup). This is all good and nice. My result will look something like this: partno status cmupqty test1 stock 10.00 15 test1 incoming15.00 71 test2 incoming12.00 10 Now, I need the first line to say "15.00" in the cmup field. That is, stock and incoming are obviously not being grouped, but since it's the same partno I'd like somehow to show the highest cmup. Is there some black SQL voodoo that'll achieve this ? TIA, -- Best Regards, Michael L. Hostbaek */ PGP-key available upon request /* pgpfuewP5VRxX.pgp Description: PGP signature
[SQL] Delayed result from another connection
Dear Gurus, I have a strange scenario that doesn't seem to work flawlessly. I think I can produce a full working example if needed. We use postgresql 7.4.5 It's something like this: %--- cut here ---% CREATE TABLE php_retval(id serial PRIMARY KEY, retval varchar); CREATE FUNCTION php_run(int, varchar) RETURNS varchar AS ' declare seq ALIAS FOR $1; php ALIAS FOR $2; ret varchar; begin perform php_run_c(php, seq); ret := retval from php_retval where id = seq; return ret; end;' LANGUAGE 'plpgsql' VOLATILE STRICT; %--- cut here ---% Now, this calls a c (SPI) function that calls system() to execute "php -q" The php connects to the database and updates retval where id=seq. It seems to be OK, but the function returns the value of php_retval.retval _before_ the call. However, the php does the update. If I repeat the function call in the transaction, the new result is returned: %--- cut here ---% UPDATE php_retval SET retval='nothing' WHERE id=1; BEGIN; SELECT php_run(1, 'test.php'); --> 'nothing' SELECT php_run(1, 'test.php'); --> '3', the right value END; %--- cut here ---% I thought it's something about "35.2 Visibility of Data Changes", but that's only for triggers, isn't it? Also, the rule "query sees results of any previously started queries" seems to be invaded: the perform runs right before the query for retval. Any ideas, explanations, clarifications, points to earlier discussions, rtfm etc? TIA, G. %--- cut here ---% \end ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Group by and aggregates
Michael L. Hostbaek wrote: List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int My SQL select statement will then group together partno, status and aggregate sum(qty) and max(cmup). This is all good and nice. My result will look something like this: partno status cmupqty test1 stock 10.00 15 test1 incoming15.00 71 test2 incoming12.00 10 Now, I need the first line to say "15.00" in the cmup field. That is, stock and incoming are obviously not being grouped, but since it's the same partno I'd like somehow to show the highest cmup. Is there some black SQL voodoo that'll achieve this ? You *CAN* sort by aggregates e.g. select partno, status, sum(cmup) as cmup, sum(qty) as qty from my_table group by partno, status order by partno, sum(cmup) desc; partno | status | cmup | qty +--+--+- test1 | incoming | 29 | 71 test1 | stock| 10 | 15 test2 | incoming | 12 | 10 -- Edmund Bacon <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Group by and aggregates
If I understand well, you want the highest cmup for each partno, that is max(cmup) grouped by partno (only). You can achieve this with a subselect, and then you join the results whith the query you already have: SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS max_cmup, sum(T.qty) AS sum_qty FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM my_table GROUP BY partno) AS TMP WHERE tmp.partno=T.partno GROUP BY T.partno, TMP.max_cmup_for_partno, T.status Hope it helped. On Thu, 2004-11-04 at 13:54, Michael L. Hostbaek wrote: List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int Here a small sample of contents in my table: some_id partno status cmup qty 1 test1 stock 10.00 15 2 test2 incoming 12.00 10 3 test1 incoming 15.00 60 4 test1 incoming 14.00 11 My SQL select statement will then group together partno, status and aggregate sum(qty) and max(cmup). This is all good and nice. My result will look something like this: partno status cmup qty test1 stock 10.00 15 test1 incoming 15.00 71 test2 incoming 12.00 10 Now, I need the first line to say "15.00" in the cmup field. That is, stock and incoming are obviously not being grouped, but since it's the same partno I'd like somehow to show the highest cmup. Is there some black SQL voodoo that'll achieve this ? TIA, signature.asc Description: This is a digitally signed message part
Re: [SQL] Group by and aggregates
On Thu, Nov 04, 2004 at 05:54:30PM +0100, Michael L. Hostbaek wrote: > some_id partno status cmupqty > 1 test1 stock 10.00 15 > 2 test2 incoming12.00 10 > 3 test1 incoming15.00 60 > 4 test1 incoming14.00 11 > > My SQL select statement will then group together partno, status and > aggregate sum(qty) and max(cmup). This is all good and nice. It would be helpful to see the exact query you're running. Based on the query output you posted below, I'd guess your query looks like this: SELECT partno, status, MAX(cmup) AS cmup, SUM(qty) AS qty FROM my_table GROUP BY partno, status ORDER BY partno, status DESC; > My result will look something like this: > > partno status cmupqty > test1 stock 10.00 15 > test1 incoming15.00 71 > test2 incoming12.00 10 > > Now, I need the first line to say "15.00" in the cmup field. That is, > stock and incoming are obviously not being grouped, but since it's the > same partno I'd like somehow to show the highest cmup. The query I posted above duplicates this output exactly. The cmup field in the first record is 10.00 because that's the maximum value of cmup where partno='test1' and status='stock', which is how I (and presumably you) specified the grouping to work with GROUP BY. Perhaps you want to group only by partno and not by status: SELECT partno, MAX(cmup) AS cmup, SUM(qty) AS qty FROM my_table GROUP BY partno ORDER BY partno; partno | cmup | qty +---+- test1 | 15.00 | 86 test2 | 12.00 | 10 If that's not what you want, then please post the exact output you're looking for. If you want to include the status field, then please explain why a record for 'test1' and 'stock' should have a MAX(cmup) of 15.00. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Group by and aggregates
On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote: ... > some_id partno status cmupqty > 1 test1 stock 10.00 15 > 2 test2 incoming12.00 10 > 3 test1 incoming15.00 60 > 4 test1 incoming14.00 11 ... > My result will look something like this: > > partnostatus cmupqty > test1 stock 10.00 15 > test1 incoming15.00 71 > test2 incoming12.00 10 > > Now, I need the first line to say "15.00" in the cmup field. That is, > stock and incoming are obviously not being grouped, but since it's the > same partno I'd like somehow to show the highest cmup. Is there some > black SQL voodoo that'll achieve this ? junk=# select partno, status, (select max(cmup) from my_table as b where b.partno = a.partno) as cmup, sum(qty) from my_table as a group by partno, status, (select max(cmup) from my_table as b where b.partno = a.partno); partno | status | cmup | sum +--+---+- test1 | incoming | 15.00 | 71 test1 | stock| 15.00 | 15 test2 | incoming | 12.00 | 10 (3 rows) Oliver Elphick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Delayed result from another connection
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <[EMAIL PROTECTED]> writes: > The php connects to the database and updates retval where id=seq. > It seems to be OK, but the function returns the value of php_retval.retval > _before_ the call. Yes, because your transaction is working with a database snapshot that predates the other transaction run by the separate PHP connection. > I thought it's something about "35.2 Visibility of Data Changes", but that's > only for triggers, isn't it? Nope. > Also, the rule "query sees results of any > previously started queries" seems to be invaded: the perform runs right > before the query for retval. Prior to PG 8.0, new snapshots are not taken between commands of a function, even in READ COMMITTED mode. You could get the behavior you want by issuing separate interactive commands instead of wrapping the sequence in a function. This has been a sore spot for a long time, but we didn't get consensus about changing it till recently ... regards, tom lane ---(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
