www.webdeveloper.com
Results 1 to 7 of 7

Thread: sql query advise - is this possible?

  1. #1
    Join Date
    Jun 2005
    Location
    London
    Posts
    38

    sql query advise - is this possible?

    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)

  2. #2
    Join Date
    Jun 2005
    Location
    London
    Posts
    38
    ..

  3. #3
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    1,138
    Something like the following should do you justice.
    Code:
    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.
    Chris.Martz.me
    Some things Man was never meant to know. For everything else, there's Google.

  4. #4
    Join Date
    Jun 2005
    Location
    London
    Posts
    38
    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 (
    Code:
    -------|----------|-------|------------------|--------|
    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
    Code:
    -------|----------|-------|------------------|--------|
    site   |  Month   | Year  |   Search Term    | Count |
    -------|----------|-------|------------------|--------|
    site 1 |   Jan     | 2005 |   Photography   |  2000 |
    site 2 |   Jan     | 2005 |   Photography   |  2000 |
    Last edited by NogDog; 01-11-2006 at 11:29 AM. Reason: formatting with code tags

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,936
    Sounds like you want to use GROUP BY with a SUM():
    Code:
    SELECT SUM(searches.search_count) AS count,...
    WHERE...
    GROUP BY searches.site_id
    ...
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    1,138
    I agree.
    Chris.Martz.me
    Some things Man was never meant to know. For everything else, there's Google.

  7. #7
    Join Date
    Jun 2005
    Location
    London
    Posts
    38
    cheers, that did the trick!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles