[posted and mailed, please reply in news]

Paul (pchronos@hotmail.com) writes:
> vI have a simple table that contains Item codes, qty units, prices and
> proce dates as follows:
>
> ItemA, 1, 7.75, 2003/08/03
> ItemA, 2, 9.22, 2003/08/04
> ItemA, 5, 7.25, 2003/08/02
> ItemA, 4, 8.25, 2003/08/01
> ItemB, 3, 3.35, 2003/08/03
> ItemB, 9, 3.25, 2003/08/05
> ItemC, 3, 0.98, 2003/08/07
> ItemC, 7, 0.78, 2003/08/03
> ItemC, 4, 0.48, 2003/08/02
> etc..
>
> I am trying to write a query that will return one line per item (
> GROUP BY Item ) with the sum of units for each group and the price of
> the MAX date for each group.
>
> ex: For the above the result should be be:
>
> ItemA, 12.00, 9.22
> ItemB, 12.00, 3.25
> ItemC, 14.00, 0.98

SELECT b.item, b.cnt, a.price
FROM tbl a
JOIN (SELECT item, cnt = SUM(qty), lastdate = MAX(date)
FROM tbl
GROUP BY item) AS b ON a.item = b.item
AND a.date = b.lastdate

Note this solution is not tested. Had you included a CREATE TABLE
statement and INSERT statements with sample data, I would have
tested it.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

select item
, sum(qty)
, max(prices)
from item
group by item

this will also work



Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93D13ACAEDYazorman@127.0.0.1>...
> [posted and mailed, please reply in news]
>
> Paul (pchronos@hotmail.com) writes:
> > vI have a simple table that contains Item codes, qty units, prices and
> > proce dates as follows:
> >
> > ItemA, 1, 7.75, 2003/08/03
> > ItemA, 2, 9.22, 2003/08/04
> > ItemA, 5, 7.25, 2003/08/02
> > ItemA, 4, 8.25, 2003/08/01
> > ItemB, 3, 3.35, 2003/08/03
> > ItemB, 9, 3.25, 2003/08/05
> > ItemC, 3, 0.98, 2003/08/07
> > ItemC, 7, 0.78, 2003/08/03
> > ItemC, 4, 0.48, 2003/08/02
> > etc..
> >
> > I am trying to write a query that will return one line per item (
> > GROUP BY Item ) with the sum of units for each group and the price of
> > the MAX date for each group.
> >
> > ex: For the above the result should be be:
> >
> > ItemA, 12.00, 9.22
> > ItemB, 12.00, 3.25
> > ItemC, 14.00, 0.98
>
> SELECT b.item, b.cnt, a.price
> FROM tbl a
> JOIN (SELECT item, cnt = SUM(qty), lastdate = MAX(date)
> FROM tbl
> GROUP BY item) AS b ON a.item = b.item
> AND a.date = b.lastdate
>
> Note this solution is not tested. Had you included a CREATE TABLE
> statement and INSERT statements with sample data, I would have
> tested it.