Thread: Group by and aggregates

Group by and aggregates

From
"Michael L. Hostbaek"
Date:
List,

I've got a table looking something like this:

my_tablesome_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,

--
Best Regards,Michael L. Hostbaek
*/ PGP-key available upon request /*

Re: Group by and aggregates

From
Michael Fuhr
Date:
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 |  86test2  | 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/


Re: Group by and aggregates

From
Edmund Bacon
Date:
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
> 
> 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 ?
>

You *CAN* sort by aggregates
e.g.

select partno, status, sum(cmup) as cmup, sum(qty) as qty   from my_table   group by partno, status   order by partno,
sum(cmup)desc;
 
 partno |  status  | cmup | qty
--------+----------+------+----- test1  | incoming |   29 |  71 test1  | stock    |   10 |  15 test2  | incoming |   12
| 10
 


-- 
Edmund Bacon <ebacon@onesystem.com>


Re: Group by and aggregates

From
Franco Bruno Borghesi
Date:
If I understand well, you want the highest cmup for each partno, that is max(cmup) grouped by partno (only). <br /><br
/>You can achieve this with a subselect, and then you join the results whith the query you already have:<br /><br />
SELECTT.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS max_cmup, sum(T.qty) AS sum_qty<br /> FROM my_table
T,(SELECT partno, max(cmup) AS max_cmup_for_partno FROM my_table GROUP BY partno) AS TMP<br /> WHERE
tmp.partno=T.partno<br/> GROUP BY T.partno, TMP.max_cmup_for_partno, T.status<br /><br /> Hope it helped.<br /><br
/><br/> On Thu, 2004-11-04 at 13:54, Michael L. Hostbaek wrote: <blockquote type="CITE"><pre><font
color="#737373"><i>List, 

I've got a table looking something like this:

my_tablesome_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, </i></font></pre></blockquote>

Re: Group by and aggregates

From
Oliver Elphick
Date:
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 |  71test1  | stock    | 15.00 |  15test2  | incoming |
12.00|  10
 
(3 rows)

Oliver Elphick




Re: Group by and aggregates

From
"Michael L. Hostbaek"
Date:
Franco Bruno Borghesi (franco) writes:
> If I understand well, you want the highest cmup for each partno, that is
> max(cmup) grouped by partno (only).
>
> 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.

This worked out nicely. Thank you very much !

/mich

Re: Group by and aggregates

From
Sam Mason
Date:
Michael L. Hostbaek wrote:
>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 ?

I think you need to join the table back on itself to get the total
for each part.  Not a very impressive incantation, but I'd do it 
in SQL like this:
 SELECT x.partno, x.status, y.tot, sum(x.qty) FROM my_table x, (   SELECT partno, MAX(cmup) as tot   FROM my_table
GROUPBY partno) y WHERE x.partno = y.partno GROUP BY x.partno, x.status, y.tot
 

Cheers, Sam