Group by and aggregates

--jI8keyz6grp/JLjh
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

List,=20

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,=20

--=20
Best Regards,
Michael L. Hostbaek=20

*/ PGP-key available upon request /*

--jI8keyz6grp/JLjh
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (FreeBSD)

iD8DBQFBil7GewpSEg9V9r4RArYRAJ9iLpbopnu0S8YqYexqtI5sdCb+/ACf SwbQ
0jPdwravHSc//5t6mCXwaN0=
=gbgG
-----END PGP SIGNATURE-----

--jI8keyz6grp/JLjh--

Re: Group by and aggregates

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/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Group by and aggregates

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Group by and aggregates

--=-1dkCXoWiqa8yxr6ZutDb
Content-Type: multipart/alternative; boundary="=-8sCHNVhIXYGgzNRy7sNC"


--=-8sCHNVhIXYGgzNRy7sNC
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

If I understand well, you want the highest cmup for each partno, that is
max(cmup) grouped by partno (only).=20

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=3DT.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,=20
>=20
> I've got a table looking something like this:
>=20
> my_table
> some_id int bla bla,
> partno varchar(100),
> status varchar(100),
> cmup numeric(14,2),
> qty int
>=20
> Here a small sample of contents in my table:
>=20
> 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
>=20
>=20
> My SQL select statement will then group together partno, status and
> aggregate sum(qty) and max(cmup). This is all good and nice.
>=20
> My result will look something like this:
>=20
> partno status cmup qty
> test1 stock 10.00 15
> test1 incoming 15.00 71
> test2 incoming 12.00 10
>=20
> 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 ?
>=20
> TIA,=20

--=-8sCHNVhIXYGgzNRy7sNC
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable








If I understand well, you want the highest cmup for each partno, that is ma=
x(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_t=
able GROUP BY partno) AS TMP

WHERE tmp.partno=3DT.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,=20



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, 

Re: Group by and aggregates

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



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Group by and aggregates

--Kj7319i9nmIyA2yE
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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).=20
>=20
> 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=3DT.partno
> GROUP BY T.partno, TMP.max_cmup_for_partno, T.status
>=20
> Hope it helped.

This worked out nicely. Thank you very much !

/mich

--Kj7319i9nmIyA2yE
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (FreeBSD)

iD8DBQFBi5OCewpSEg9V9r4RAh9nAJ45FD6GWngd1EOvYQiUxlxULFmGqACf cqj+
BhQmt0IL5x1jH+iG3uMImsM=
=u+0w
-----END PGP SIGNATURE-----

--Kj7319i9nmIyA2yE--