Date: Thu, 4 Nov 2004 17:54:30 +0100
From: "Michael L. Hostbaek" <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Group by and aggregates
Message-ID: <20041104165430.GD94770@mich2.itxmarket.com>
Mail-Followup-To: "Michael L. Hostbaek" <mich@the-lab.org>,
	pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="jI8keyz6grp/JLjh"
Content-Disposition: inline
User-Agent: Mutt/1.5.6i


--jI8keyz6grp/JLjh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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,=20

--=20
Best Regards,
	Michael L. Hostbaek=20

	*/ PGP-key available upon request /*

--jI8keyz6grp/JLjh
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (FreeBSD)

iD8DBQFBil7GewpSEg9V9r4RArYRAJ9iLpbopnu0S8YqYexqtI5sdCb+/ACfSwbQ
0jPdwravHSc//5t6mCXwaN0=
=gbgG
-----END PGP SIGNATURE-----

--jI8keyz6grp/JLjh--