Date: Mon, 4 Apr 2005 12:16:47 +0200
From: "Michael L. Hostbaek" <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: select & group by
Message-ID: <20050404101647.GJ23648@mich2.itxmarket.com>
Mail-Followup-To: "Michael L. Hostbaek" <mich@the-lab.org>,
	pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.9i

I've got a problem selecting some specific data from my table. Imagine
the following rows:

part	| mfg	| qty	| price	| eta	
---------------------------------------
TEST1	 ABC	 10	 100		(No ETA, as item is in stock)
TEST1	 ABC	 12	 120	 04/04
TEST2	 CBA	 17	 10	 05/05
TEST2	 CBA	 10	 20		(No ETA, as item is in stock)


I'd like my selection to produce the following result:

part	| mfg	| qty	| qty incoming	| highest price	| eta
-------------------------------------------------------------
TEST1	 ABC	 10	 12		 120		 04/04
TEST2	 CBA	 10	 17		 20		 05/05

Any clues on how to do this ? I kow the group by part, mfg, max(price) -
but I do not know how to deal with the splitting up qty and stock qty
and incoming qty.

Thanks.

/mich