X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 364B53A48D1 for ; Thu, 4 Nov 2004 17:41:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53896-02 for ; Thu, 4 Nov 2004 17:41:48 +0000 (GMT) Received: from lucifer.oficina (unknown [200.68.121.201]) by svr1.postgresql.org (Postfix) with ESMTP id 3B19E3A48F4 for ; Thu, 4 Nov 2004 17:41:46 +0000 (GMT) Received: from localhost (localhost.akyasociados.com.ar [127.0.0.1]) by lucifer.oficina (8.13.1/8.12.10) with ESMTP id iA4Hflq7008111; Thu, 4 Nov 2004 14:41:47 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Received: from lucifer.oficina ([127.0.0.1]) by localhost (lucifer.oficina [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 06438-22; Thu, 4 Nov 2004 14:41:46 -0300 (ART) Received: from [192.168.1.168] (taz.oficina [192.168.1.168]) (authenticated bits=0) by lucifer.oficina (8.13.1/8.12.10) with ESMTP id iA4HfgPq008106; Thu, 4 Nov 2004 14:41:42 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: Group by and aggregates From: Franco Bruno Borghesi To: "Michael L. Hostbaek" Cc: pgsql-sql@postgresql.org In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com> References: <20041104165430.GD94770@mich2.itxmarket.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-1dkCXoWiqa8yxr6ZutDb" Organization: AK y Asociados S.R.L. Message-Id: <1099590115.1475.5.camel@taz.oficina> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 04 Nov 2004 14:41:55 -0300 X-Virus-Scanned: by amavisd-new at akyasociados.com.ar X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_FONTCOLOR_UNSAFE, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/38 X-Sequence-Number: 19357 --=-1dkCXoWiqa8yxr6ZutDb Content-Type: multipart/alternative; boundary="=-8sCHNVhIXYGgzNRy7sNC" --=-8sCHNVhIXYGgzNRy7sNC Content-Type: text/plain Content-Transfer-Encoding: quoted-printable If I understand well, you want the highest cmup for each partno, that is max(cmup) grouped by partno (only).=20 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=3DT.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,=20 >=20 > I've got a table looking something like this: >=20 > my_table > some_id int bla bla, > partno varchar(100), > status varchar(100), > cmup numeric(14,2), > qty int >=20 > Here a small sample of contents in my table: >=20 > 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 >=20 >=20 > My SQL select statement will then group together partno, status and > aggregate sum(qty) and max(cmup). This is all good and nice. >=20 > My result will look something like this: >=20 > partno status cmup qty > test1 stock 10.00 15 > test1 incoming 15.00 71 > test2 incoming 12.00 10 >=20 > 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 ? >=20 > TIA,=20 --=-8sCHNVhIXYGgzNRy7sNC Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable If I understand well, you want the highest cmup for each partno, that is ma= x(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_t= able GROUP BY partno) AS TMP
WHERE tmp.partno=3DT.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,=20

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,

--=-8sCHNVhIXYGgzNRy7sNC-- --=-1dkCXoWiqa8yxr6ZutDb Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (FreeBSD) iD8DBQBBimnj21dVnhLsBV0RAp1NAJ4kfWgZADdvVN43nFVwov2C4/fH0wCeJZUr KEYGQcZVOq8dsDa9igDX//Y= =RchV -----END PGP SIGNATURE----- --=-1dkCXoWiqa8yxr6ZutDb--