From: Michael L. Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Group by and aggregates
Date: Thu, 4 Nov 2004 17:54:30 +0100
Message-ID: <20041104165430.GD94770@mich2.itxmarket.com> (raw)
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,
--
Best Regards,
Michael L. Hostbaek
*/ PGP-key available upon request /*
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mich@the-lab.org
Subject: Re: Group by and aggregates
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox