grouping by domain in referer table
- I've got a lot of referer variables in my dbase and I'd like to have a query that groups them just by the domain. Everything up to the 3rd "/" should do it. So far, I'm using:
SELECT COUNT(id) AS clicktotal, ref
FROM clicklogs
GROUP BY ref
This only lists everything by the actual referring url. Is there a way to only have it group by the domain within the referring url? Thanks.
-
Sounds like you are going to have to do field parsing to determine the group by value. I would research the PATINDEX or CHARINDEX and probably a couple of different functions that they will reference to get the 3 \ information to group by
Good Hunting!AJ Ahrens
webmaster@kritter.net -
--I don't know why I did this
but I guess i feel a bit lethargic at work right now...--You can try something like this...
--I don't expect anyone to decipher it though
/********************* splitting the referrals into pieces **********************/
set nocount on
create table #clicklogs( ref varchar(255) )
create table #doms( dom varchar(20) ) -- just for generating test data
insert #doms select 'abcde' union select 'fg' union select 'hijklmopq' doms
insert #clicklogs -- generate test data
select top 200 d1.dom+'/sub'+d2.dom+'/subsub'+d3.dom+'/subsub'+d4.dom+'/subsubsub'+d5.dom
from #doms d1 cross join #doms d2 cross join #doms d3 cross join #doms d4 cross join #doms d5
order by newid()
select
--splittable.ref,
--splittable.domlvl1,
--splittable.domlvl2,
splittable.domlvl3,
count(*)
from
(select
lvl2.ref,
lvl2.domlvl1,
lvl2.domlvl2,
substring(lvl2.ref,1,charindex('/',lvl2.ref,len(lvl2.domlvl2)+1) ) as domlvl3
from
(select
lvl1.ref,
lvl1.domlvl1,
substring(lvl1.ref,1,charindex('/',lvl1.ref,len(lvl1.domlvl1)+1) ) as domlvl2
from
( select ref, substring(ref,1,charindex('/',ref)) as domlvl1 from #clicklogs
) as lvl1
) as lvl2
) as splittable
group by
--splittable.ref
--splittable.domlvl1
--splittable.domlvl2
splittable.domlvl3
order by
count(*) desc
--splittable.ref
--splittable.domlvl1
--splittable.domlvl2
--splittable.domlvl3
drop table #clicklogs
drop table #doms
set nocount off
/*********************************************************************/
--You must unlearn what You have learnt.
--/rockmoose
You must unlearn what You have learnt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply