Counting records by grouping

[TechBoyJK]

Hi Guys,

I have a hit counter built in to my website so I can keep statistics on traffic coming to certain pages. The service works similar to bit.ly

I am trying to compile some stats about the http_referer collected when a hit is counted. If you go to the following link, and click the 'referer' tab under stats, you can see how the host/string and respective hits are calcuated.

http://bit.ly/cGXNq3+

Here is an example referer and below it is how I split it up for collection in the database

http://forums.devshed.com/coldfusion-development-84/

referer_host - forums.devshed.com
referer_string - coldfusion-development-84/

Here is the table structure for the hit records


tbl_hits

hit_id
redirect_id
hit_datetime
referer_host
referer_string
referer_type
location
ip

Want to group referer_string hit results according to the referer_host they belong to..

example; 4 hits come in from 3 different referers

1 http://forums.devshed.com/coldfusion-development-84/
2 http://forums.devshed.com/php-development-5/
3 http://forums.devshed.com/java-help-9/
4 http://forums.devshed.com/php-development-5/

This would result in

forums.devshed.com - 4 hits (total)
-coldfusion-development-84/ - 1 hit
-php-development-5/ - 2 hits
-java-help-9/ - 1 hit


1. Need total hits where 'id' = id and referer_host = referer_host
2. Need total hits where 'id' = id and referer_host = referer_host and referer_string = referer_string


I'm not sure if I can compile this data with just one query. If anybody has any ideas on how to write this out, I'd be very grateful. I'm trying to figure it out on my own but a query like this is just over my head at this time.

 

[Blueoak]

I can't guarantee this is bug free, but it'll get you pretty close to what you're looking for.

SELECT t.referer_host, t.referer_string, sub.referer_host_count, COUNT(*) AS referer_string_count
FROM tbl_hits t
INNER JOIN
(
	SELECT referer_host, COUNT(*) AS referer_host_count
	FROM tbl_hits
	GROUP BY referer_host
) sub ON t.referer_host = sub.referer_host
GROUP BY t.referer_host, t.referer_string
ORDER BY t.referer_host, t.referer_string

referer_host		referer_string			referer_host_count	referer_string_count
forums.devshed.com	-coldfusion-development-94	4			1
forums.devshed.com	-java-help-9			4			1
forums.devshed.com	-php-development		4			2

 

[TechBoyJK]

thansk! I will play with it and see how far it gets me.

 

[TechBoyJK]

ok... I'm getting the following sql error

Column 'sub.referer_host_count' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas?

 

[Jeraden]

Just add that field to the last group_by at the bottom

 

[TechBoyJK]

cool thanks! i got the query to pass, and am looking at a dump of the results right now. might just do the trick! thanks guys!

 

[TechBoyJK]

n/m this link im just testing something

details.at/test123456

 

[TechBoyJK]

ok.. i had to add 2 "where id = #id#" clauses to get it to add hits right.. but i think i got it.