Any help here, have the following tables that are used to log all searches carried out on several websites each month.
Keywords - has 2 fields : id(int) and search_term(varchar)
Sites - has 2 fields id(int) and site(varhar)
Searches - has 6 fields : id(int), site_id(int),month(int),year(int),search_term(int)
I have written queries that
1) output the searches for each site for any month months and
2) output the top searches on a site in the last year.
However, query 2 shown below will display results such as the ones shown below:
SELECT searches.search_count AS count, keywords.search_term AS search, searches.month AS month, searches.year AS year
FROM searches, sites,keywords
WHERE keywords.id = searches.search_term AND searches.site_id = sites.id AND sites.site = '".$input['0']."'
ORDER BY searches.search_count DESC LIMIT 0,100";
November 2005 education 146
September 2005 education 136
October 2005 google 132
December 2005 education 132
October 2005 education 128
August 2005 map 126
November 2005 google 116
My question is - Is there a way to query this database so that rather than displaying the results above it will display the results below:
i.e. adding up the keywords searches for the whole year rather than displaying the top monthly searches.
Otherwise I will have to create an additional table: