MySQL

Need help with grouping

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2008

Posts: 2

Reputation: drewtemp is an unknown quantity at this point

Solved Threads: 0

drewtemp

Offline

Newbie Poster

Need help with grouping

Aug 13th, 2008

Hey guys,

I need a "little" help with a query. I'm trying to pull information regarding referrals from search engines which grab the keywords. For example, two people search for foobar and another searches for barfoo. I would need the results returned similar to this:

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

Hits   |      Page URL       |   Keywords
=========================
  3    | http://url.com/     |         2

Here is the query I currently have which fails miserably.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT A.page_url, A.page_title, B.total_terms, B.hits
	FROM visits A
	INNER JOIN
		(SELECT search_terms, search_temrs, COUNT(*) AS hits
			FROM searches
			GROUP BY url_path, search_terms
		) B
		ON A.page_url = B.url_path
	GROUP BY A.page_url, B.search_terms
SELECT A.page_url, A.page_title, B.total_terms, B.hits
	FROM visits A
	INNER JOIN
		(SELECT search_terms, search_temrs, COUNT(*) AS hits
			FROM searches
			GROUP BY url_path, search_terms
		) B
		ON A.page_url = B.url_path
	GROUP BY A.page_url, B.search_terms

Which returns:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
	HITS 	PAGE_TITLE 	PAGE_URL 	TOTAL_TERMS
1 	 1 	 	page 	 	http://url.com/ 	 	1
2 	 2 	 	page 	 	http://url.com/  		2
	HITS 	PAGE_TITLE 	PAGE_URL 	TOTAL_TERMS
1 	 1 	 	page 	 	http://url.com/ 	 	1
2 	 2 	 	page 	 	http://url.com/  		2