Scalar functions question

[junkmail]

I want to be able to sum a value returned from a scalar function but I get the following error. Can someone tell me the correct way to do this?


Cannot perform an aggregate function on an expression containing an aggregate or a subquery


dbo.get_price is my scalar function I created.


below is the select statement.

SELECT [Order Number],sum((select dbo.get_price([order date],[qty ordered]))) as price,sum([Final Ship Cost]) as [ship cost],
(select case when [shipping method]<>'San Diego Local Delivery' then 0 else sum([misc cost]) end) as [delivery Fee],[ship city],[ship state],
(select * from get_tax_rate([ship city],[ship state])) as [Tax Rate]
FROM millennium
where exists (select item from items where item=[product name] ) and
([ship date]>=@stdate and [ship date]<=@enddate)

group by [Order Number],[shipping method],[ship city],[ship state]

 

[gk53]

2 problems in you SQL
1. if you use user defined function in your sql statement you do not need extra select...
2. I.m not sure what is (select * from get_tax_rate([ship city],[ship state])) as [Tax Rate], but if it is scalar function you do not need select * from and need dbo before function name. If it is table valued function it is not apropriate use of function you must use apply statement

SELECT [Order Number],
		sum((dbo.get_price([order date],[qty ordered]))) as price,
		sum([Final Ship Cost]) as [ship cost], 
		(select case when [shipping method]<>'San Diego Local Delivery' then 0 
				else sum([misc cost]) end) as [delivery Fee],
		[ship city],
		[ship state],
		(select * from get_tax_rate([ship city],[ship state])) as [Tax Rate]
		FROM millennium
where exists (select item from items where item=[product name] ) 
	and	([ship date]>=@stdate and [ship date]<=@enddate)

group by [Order Number],[shipping method],[ship city],[ship state]

 

[junkmail]

Thanks for your help gk53.