Help with a query please

[desjardins2010]

I'm trying to structure this query properly but failing with all attempts. 

 

i have a table that has 5 fields id, http_referrer,ip,hit,today

 

what I want to do is query this table to show me all the http_referrer's GROUPED up so only 1 row for ALL say 255 results that came from www.google.ca and the SUM'd hits 

 

so 

 

RFERRER          HIT

google.ca           255

phpfreaks.com   15

 

I tried 

SELECT SUM(hit) as 'hits' FROM `global_stats` GROUP BY `http_referrer`

this don't work

[Barand]

try

SELECT http_referrer, SUM(hit) as 'hits' FROM `global_stats` GROUP BY `http_referrer`

[desjardins2010]

Ok this worked BUT one more question regarding this I notice that I get alot of sites that are the same CORE site but different appending ID's so they showing as different sites... is there an easy way to just match the www.something.com and leave our the ?asdfmsdmfs

 

 

just curious no biggy I can handle if not

[Psycho]

Ok this worked BUT one more question regarding this I notice that I get alot of sites that are the same CORE site but different appending ID's so they showing as different sites... is there an easy way to just match the www.something.com and leave our the ?asdfmsdmfs

 

 

just curious no biggy I can handle if not

 

You could use RegEx in your query, but that is a sub-optimal solution since RegEx will be slow. Instead, you should handle this when saving the records to your database by stripping off the extraneous bit. If you do need the full URL then you should add a column and put the base URL in one and the parameters of the URL in the other.

[Barand]

try like this

SELECT SUBSTRING_INDEX(http_referrer, '?', 1) as referrer
, SUM(hit) as hits
FROM global_stats
GROUP BY referrer

[desjardins2010]

Sweet that worked perfect thanks Barand