aggregate function on an expression with sub q

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

aggregate function on an expression with sub q

Author

Topic

moooris
Starting Member

11 Posts

Posted - 2009-06-29 : 06:11:34

Hi, im trying to compile this function but i'm getting:

Msg 8120, Level 16, State 1, Procedure fGetOrderTotalPrice, Line 11
Column 'dbo.StockTracking.AdvancedProduct' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 130, Level 15, State 1, Procedure fGetOrderTotalPrice, Line 11
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I was looking for some result in google, but it just made it more complicated for me.
Any help would be greatly appreciated.


ALTER FUNCTION [dbo].[fGetOrderTotalPrice](@OrderID INT)

RETURNS SMALLMONEY

AS
BEGIN

	
	DECLARE @PriceToReturn SMALLMONEY
	
	SET @PriceToReturn = (
		SELECT CASE WHEN dbo.StockTracking.AdvancedProduct IS NULL 
	THEN SUM ((dbo.StockPurchases.TotalPrice/dbo.StockPurchases.Quantity)*dbo.StockTracking.Quantity) 
    ELSE SUM(((dbo.StockPurchases.TotalPrice/dbo.StockPurchases.Quantity) / (SELECT Fraction FROM Stock.AdvancedProducts WHERE Id = dbo.StockTracking.AdvancedProduct)) *dbo.StockTracking.Quantity)  

	END
	FROM dbo.StockPurchases 
	INNER JOIN dbo.StockTracking ON dbo.StockPurchases.Id = dbo.StockTracking.PurchaseID
	LEFT OUTER JOIN  Stock.AdvancedProducts ON dbo.StockTracking.AdvancedProduct = Stock.AdvancedProducts.Id
        --dbo.StockTracking.AdvancedProduct can be null, so i  used outer join (is this a correct move?)
	WHERE dbo.StockTracking.OrderID = @OrderID)
	
	RETURN ISNULL(@PriceToReturn,0)
	
END

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 06:34:43

[code]ALTER FUNCTION dbo.fGetOrderTotalPrice
(
@OrderID INT
)
RETURNS MONEY
AS
BEGIN
RETURN (
SELECT SUM(sp.TotalPrice / sp.Quantity / COALESCE(ap.Fraction, 1) * st.Quantity)
FROM dbo.StockPurchases AS sp
INNER JOIN dbo.StockTracking AS st ON st.PurchaseID = sp.ID
LEFT JOIN Stock.AdvancedProducts AS ap ON ap.ID = st.AdvancedProduct
WHERE st.OrderID = @OrderID
)
END[/code]

N 56°04'39.26"
E 12°55'05.63"

moooris
Starting Member

11 Posts

Posted - 2009-06-29 : 06:56:09

Awesome! working perfect.
Thanks a lot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 06:56:38

And faster too?

N 56°04'39.26"
E 12°55'05.63"

moooris
Starting Member

11 Posts

Posted - 2009-06-29 : 07:46:31

The first one did not work at all, so i cannot compare (although im sure it would have been terrible)
But this one you gave is very fast :D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:51:48

The only thing to look out for is when Fraction equals zero.
But that is not an option, right?

N 56°04'39.26"
E 12°55'05.63"

moooris
Starting Member

11 Posts

Posted - 2009-06-29 : 08:10:09

Yea it should not happen unless the user would be too dumb.
but thanks for pointing me on that, i should secure it from the software or even with a trigger

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 08:13:17

Or change the code.
How should a Fraction of zero be treated? Just as there is no value?

ALTER FUNCTION dbo.fGetOrderTotalPrice
(
	@OrderID INT
)
RETURNS MONEY
AS
BEGIN
	RETURN	(
			SELECT		SUM(sp.TotalPrice / sp.Quantity / COALESCE(NULLIF(ap.Fraction, 0), 1) * st.Quantity) 
			FROM		dbo.StockPurchases AS sp
			INNER JOIN	dbo.StockTracking AS st ON st.PurchaseID = sp.ID
			LEFT JOIN	Stock.AdvancedProducts AS ap ON ap.ID = st.AdvancedProduct
			WHERE		st.OrderID = @OrderID
		)	
END

N 56°04'39.26"
E 12°55'05.63"

moooris
Starting Member

11 Posts

Posted - 2009-06-29 : 08:40:09

Very nice trick there (:

In theory if the fraction is 0 they should not have used the so called "AdvancedProduct" in first place.
But securing the code more is just better i guess, and im just learning more from that. so thank you!

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:22:30

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources