sql server (microsoft): Using result of aggregate function against each record? -- sql server (microsoft)

Using result of aggregate function against each record?

zapp0 NO[at]SPAM email.si
11/17/2003 4:24:25 AM

sql server (microsoft):

Dear All,

is there any other (better) way to perform this kind of query?
Is there a way to do this in a single query?

A simple example of the situation (on northwind DB):

DECLARE @OrdersTotal MONEY
SELECT
@OrdersTotal = SUM(UnitPrice*Quantity*(1-Discount))
FROM
"Order Details"
WHERE
ProductID = 11

SELECT
OrderID,
UnitPrice*Quantity*(1-Discount) / @OrdersTotal AS SomeStatistics
FROM
"Order Details"
WHERE
ProductID = 11

TIA,

Re: Using result of aggregate function against each record?

johnston NO[at]SPAM mounet.com
11/17/2003 2:07:47 PM

You could create a UDF that returns a money

CREATE FUNCTION fn_GetSum (@ProdID int)
RETURNS money
AS
BEGIN
RETURN (SELECT SUM(UnitPrice*Quantity*(1-Discount))
FROM
[Order Details]
WHERE
ProductID = @ProdID)
END

Then call the fn_GetSum in your final select statement.

SELECT
OrderID,
UnitPrice*Quantity*(1-Discount) / dbo.fn_GetSum(11)
FROM
[Order Details]
WHERE
ProductID = 11

V/R Jim Johnston

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (microsoft) group.