Subject: Re: Group by and aggregates
From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: "Michael L. Hostbaek" <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
References: <20041104165430.GD94770@mich2.itxmarket.com>
Content-Type: text/plain
Message-Id: <1099590407.5445.24.camel@braydb>
Mime-Version: 1.0
Date: Thu, 04 Nov 2004 17:46:47 +0000
Content-Transfer-Encoding: 7bit

On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote:
...
> 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 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 ?

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