Grouping by Part of a Field? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping by Part of a Field?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-10 : 17:23:21

I am trying to run a query on our database to see how commonly we receive submissions from particular domains, but can't seem to get the grouping correct.

 
Select  
	Right(Email, LEN(EMail) - PATINDEX('%@%', Email)), 
	Found = Count(*) 
From tblOrders Group By Right(Email, LEN(EMail) - PATINDEX('@', Email)), EMail 
Order By Found Desc

I end up with results such as below:
hotmail.com 570
hotmail.com 569
adelphia.net 545
host.com 534
null.com 479
aol.com 471
aol.com 435

For some reason it won't put the domain names together, and I believe it's because of the EMail in the Group By, but unfortunately I cannot remove that, or else I can't get the domain name in the Select list.

Any help would be appreciated.

Thanks,
Steve

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-10 : 17:28:49

One of your PATINDEX expressions has '%@%' the other has '@'.
(Though I may have missed your point: I'm a bit drunk!)

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-10 : 17:42:01

You are correct, that information(%'s) was missing, but after changing it, it doesn't seem to have affected the query at all. I'm still getting the same results.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-10 : 18:41:40

you are still grouping by email:

Select
Right(Email, LEN(EMail) - PATINDEX('%@%', Email)),
Found = Count(*)
From tblOrders Group By Right(Email, LEN(EMail) - PATINDEX('@', Email)), EMail
Order By Found Desc

take that out and you should be fine.

- Jeff

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-11 : 16:22:39

Yes, but that was my point as before. I can't remove the Group By Email, or else the Right(Email, LEN(EMail) - PATINDEX('%@%', Email)) in the select field list will not work. I finally ended up going with the following:


Select Distinct
	domain = Right(Email, LEN(EMail) - PATINDEX('%@%', Email))
Into #Domains
From (Select Distinct Email From tblOrders) O

Select
	domain,
	Found = Count(*)
From
	#Domains d
	Inner Join (Select Distinct Email From tblOrders) O
		On d.domain = Right(O.Email, LEN(O.EMail) - PATINDEX('%@%', O.Email))
Group By
	domain
Order By
	Found Desc

Drop Table #Domains

but I still think it would be possible to do it with one statement, maybe I'm wrong though.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 17:08:47

Ooops ... didn't see that your SELECT expression and the GROUP BY expression were different.

no need for temp tables:

SELECT Domain, COUNT(*) as Found
FROM
(
select Right(Email, LEN(EMail) - PATINDEX('%@%', Email)) as Domain
)
A
GROUP BY Domain
ORDER BY COUNT(*) DESC

- Jeff

Edited by - jsmith8858 on 06/11/2003 17:09:09

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-11 : 17:19:51

so presumably the original should have read:


Select  
	Right(Email, LEN(EMail) - PATINDEX('%@%', Email)), 
	Found = Count(*) 
From tblOrders Group By Right(Email, LEN(EMail) - PATINDEX('%@%', Email))
Order By Found Desc

and the extraneous Email in the GROUP BY was needed just because the two PATINDEX expressions were different...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources