using a use function in a group by

[junkmail]

I have a function that I wrote to return a tax rate for a list of records but I want to be able to sum by the return tax rate but I get the following error "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

 

[imex]

You can post your query as it is?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[junkmail]

get_tax_rate" is the function I wrote to get the tax rate.

SELECT (select * from get_tax_rate([ship city],[ship state])) as [Tax Rate],
sum(Cost) as cost,sum([Final Ship Cost]) as [ship cost],
sum([misc cost]) as delivery,[ship city],[ship state],
FROM orders
where exists (select item from tandem_items where item=[product name] ) and
([ship date]>=@stdate and [ship date]<=@enddate)

group by (select * from get_tax_rate([ship city],[ship state]))

 

[imex]

Try:

group by [ship city], [ship state]

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[junkmail]

The problem is I need to group by the tax rate which is derived from passing those two values to the function.

 

[imex]

You tried the way suggested? Did not get the expected result?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

What columns do you want in your final output?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

Thanks for your suggestions but I was able to use a temporary table and get the proper results.