Aggregate Problem

When I run the my code in Northwind, the ExtendedPrice is wrong because
UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice in
my statement because I couldn't figure out a better aggregate function to
use.
My goal is to simply get a GROUP by Orders.OrderDate and SUM the
ExtendedPrice expression for each date. I probably need some type of
subquery, but not sure how to go about this one.
CODE (used in northwind database):
SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
[Order Details].UnitPrice * [Order Details].Quantity)) AS ExtendedPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDateHi
It may be clearer is you give your expected results, but this may be a
starting point!
SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
FROM Orders O
JOIN [Order Details] D ON O.OrderID = D.OrderID
GROUP BY O.OrderDate
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
> When I run the my code in Northwind, the ExtendedPrice is wrong because
> UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice
> in my statement because I couldn't figure out a better aggregate function
> to use.
> My goal is to simply get a GROUP by Orders.OrderDate and SUM the
> ExtendedPrice expression for each date. I probably need some type of
> subquery, but not sure how to go about this one.
> CODE (used in northwind database):
> SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
> COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
> [Order Details].UnitPrice * [Order Details].Quantity)) AS
> ExtendedPrice
> FROM Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order Details].OrderID
> GROUP BY Orders.OrderDate
>|||that did it. thx.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uty7J2Y3FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi
> It may be clearer is you give your expected results, but this may be a
> starting point!
> SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
> FROM Orders O
> JOIN [Order Details] D ON O.OrderID = D.OrderID
> GROUP BY O.OrderDate
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
>