From: Michael Fuhr <mike@fuhr.org>
To: Michael L. Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: Re: Group by and aggregates
Date: Thu, 4 Nov 2004 10:31:53 -0700
Message-ID: <20041104173153.GA20769@winnie.fuhr.org> (raw)
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
References: <20041104165430.GD94770@mich2.itxmarket.com>
On Thu, Nov 04, 2004 at 05:54:30PM +0100, 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 SQL select statement will then group together partno, status and
> aggregate sum(qty) and max(cmup). This is all good and nice.
It would be helpful to see the exact query you're running. Based
on the query output you posted below, I'd guess your query looks
like this:
SELECT partno, status, MAX(cmup) AS cmup, SUM(qty) AS qty
FROM my_table
GROUP BY partno, status
ORDER BY partno, status DESC;
> 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.
The query I posted above duplicates this output exactly. The cmup
field in the first record is 10.00 because that's the maximum value
of cmup where partno='test1' and status='stock', which is how I
(and presumably you) specified the grouping to work with GROUP BY.
Perhaps you want to group only by partno and not by status:
SELECT partno, MAX(cmup) AS cmup, SUM(qty) AS qty
FROM my_table
GROUP BY partno
ORDER BY partno;
partno | cmup | qty
--------+-------+-----
test1 | 15.00 | 86
test2 | 12.00 | 10
If that's not what you want, then please post the exact output
you're looking for. If you want to include the status field, then
please explain why a record for 'test1' and 'stock' should have a
MAX(cmup) of 15.00.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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: mike@fuhr.org, mich@the-lab.org
Subject: Re: Group by and aggregates
In-Reply-To: <20041104173153.GA20769@winnie.fuhr.org>
* 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