sql server (alternate): Can you group by a value returned by a UDF? Example:

SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'
GROUP BY Col1, MyResult

I've had no luck. Can this be done?

Try this

SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'
GROUP BY Col1, MyFunc(Col3, Col4)

I'll give it a try guys. Thanx so much for your responses.

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
[quoted text, click to view]

I tried:

CREATE FUNCTION myfun (@int integer) RETURNS int AS
BEGIN
RETURN ((@int - 10000) % 43)
END
go
SELECT dbo.myfun(OrderID), COUNT(*)
FROM Northwind..Orders
GROUP BY dbo.myfun(OrderID)
go
SELECT myfun, COUNT(*)
FROM (SELECT myfun = dbo.myfun(OrderID)
FROM Northwind..Orders) AS x
GROUP BY myfun
go
DROP FUNCTION myfun

The plans look identical.

However, the derived table saves you from having to repeat a complex
expression, and this is a good thing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

More efficient to call the function just once per row:

SELECT Col1, SUM(Col2), MyResult
FROM (SELECT Col1, Col2, MyFunc(Col3, Col4) AS MyResult
FROM SomeTable) AS Der
GROUP BY Col1, MyResult

--