From: Oliver Elphick <olly@lfix.co.uk>
To: Michael L. Hostbaek <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Group by and aggregates
Date: Thu, 04 Nov 2004 17:46:47 +0000
Message-ID: <1099590407.5445.24.camel@braydb> (raw)
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
References: <20041104165430.GD94770@mich2.itxmarket.com>
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
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: olly@lfix.co.uk, mich@the-lab.org
Subject: Re: Group by and aggregates
In-Reply-To: <1099590407.5445.24.camel@braydb>
* 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