sql query advise - is this possible?

[agent_bozo]

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)
and search_count(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:

QUERY

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";

RESULTS

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:

education 542
education 400
map 250
google 248

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:

id(int),search_term(int),site_id(int),yearly_count(int)

[agent_bozo]

..

[chrismartz]

Something like the following should do you justice.

SELECT searches.search_count AS count, keywords.search_term AS search
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";

All you have to do it take out , searches.month AS month, searches.year AS year in your select statement.

[agent_bozo]

The problem is that in this table, you will have 12 different values (Jan - Dec)
For each search term on each site i.e. the whole table (

-------|----------|-------|------------------|--------|
site   |  Month   | Year  |   Search Term    | Count |
-------|----------|-------|------------------|--------|
site 1 |   Jan     | 2005 |   Photography   |  200   |
site 1 |   Feb     | 2005 |   Photography   |  200   |
site 1 |   Mar     | 2005 |   Photography   |  200   |
site 1 |   Apr     | 2005 |   Photography   |  200   |
site 1 |   May     | 2005 |   Photography   |  200   |
site 2 |   Jan     | 2005 |   Photography   |  200   |
site 2 |   Feb     | 2005 |   Photography   |  200   |
site 2 |   Mar     | 2005 |   Photography   |  200   |
site 2 |   Apr     | 2005 |   Photography   |  200   |
site 2 |   May     | 2005 |   Photography   |  200   |
etc

So I need a query that will add up each site to give the following looking results

-------|----------|-------|------------------|--------|
site   |  Month   | Year  |   Search Term    | Count |
-------|----------|-------|------------------|--------|
site 1 |   Jan     | 2005 |   Photography   |  2000 |
site 2 |   Jan     | 2005 |   Photography   |  2000 |

[NogDog]

Sounds like you want to use GROUP BY with a SUM():

SELECT SUM(searches.search_count) AS count,...
WHERE...
GROUP BY searches.site_id
...

[chrismartz]

I agree.

[agent_bozo]

cheers, that did the trick!