473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Group By Query Help

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?

Feb 28 '06 #1
4 1172
Try this

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

Feb 28 '06 #2
On 28 Feb 2006 13:36:50 -0800, ma******@hotmail.com wrote:
Try this

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


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

--
Hugo Kornelis, SQL Server MVP
Feb 28 '06 #3
Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:
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


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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 28 '06 #4
I'll give it a try guys. Thanx so much for your responses.

Feb 28 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: George | last post by:
Hi, I am trying to write a query in Oracle which I have not done before, and are having some difficulty getting my result. Please check my query and my results. select max(note.datetime),...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
2
by: Steve | last post by:
Access 97. I have a form where there is an option group with two buttons, and a combo box. The combo box Row Source is a query. The option group has two options a) include a subset of the...
2
by: cefrancke | last post by:
I can't seem to find a straight answer for my specific issue. Any help would be appreciated. I would like to count the various items in a table where the fields have a 'group' relationship. I...
1
by: Tim Marshall | last post by:
A2003. I am getting this error message when I try to set a report's recordsource to an SQL statement or a saved querydef that uses sub-queries. I've debug.printed the SQL, and run it as a stand...
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
9
by: Brian Hampson | last post by:
I am trying to determine all the groups which the current user has permissions to add a member. Here's my code: foreach (System.DirectoryServices.SearchResult ADSearchres in...
7
by: kooch54 | last post by:
I am trying to write a script to simply query the group members in an active directory group. I need to use LDAP to make sure I capture any global global group nestings that may occur. I already...
2
by: dilippanda | last post by:
Hi, Please help me in the following query. SELECT a.bcast_id , a.bcast_file_nm ...
4
by: SjaakBanaan | last post by:
Hey all, I have a question that ought to be simple but has given me headaches for a while. I have a table with contact email addresses, say. T ID ContID Priority Emailaddress...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.