From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Michael L. Hostbaek <mich@the-lab.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Group by and aggregates
Date: Thu, 04 Nov 2004 14:41:55 -0300
Message-ID: <1099590115.1475.5.camel@taz.oficina> (raw)
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
References: <20041104165430.GD94770@mich2.itxmarket.com>
If I understand well, you want the highest cmup for each partno, that is
max(cmup) grouped by partno (only).
You can achieve this with a subselect, and then you join the results
whith the query you already have:
SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS
max_cmup, sum(T.qty) AS sum_qty
FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM
my_table GROUP BY partno) AS TMP
WHERE tmp.partno=T.partno
GROUP BY T.partno, TMP.max_cmup_for_partno, T.status
Hope it helped.
On Thu, 2004-11-04 at 13:54, Michael L. Hostbaek wrote:
> 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,
Attachments:
signature.asc (application/pgp-signature, 186B) download
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: franco@akyasociados.com.ar, mich@the-lab.org
Subject: Re: Group by and aggregates
In-Reply-To: <1099590115.1475.5.camel@taz.oficina>
* 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