Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Simple Row Counter

    It sound like a simple task to perform but I just can't seem to get it. I've built a search function on the site and after each search request, a log is kept of the search word and the date, so the table looks a little like this:

    ID  SearchTerm DateSearched
    ============================
    1   home   2004/03/09
    2   fred    &nb sp;2004/03/08
    3   cup    &nbs p;2004/03/08
    4   home   2004/03/08
    5   home   2004/03/08
    6   fred    &nb sp;2004/03/07

    I want to pull out each of the search terms as well as how many times they've been searched on so I could display it in the format like so:

    Word   Qty
    ============================
    home    3
    fred      2
    cup       1

    Any help would be appreciated. Thanks.

    Goran (GoMo)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select SearchTerm as Word, count(*) as Qty
    from YourTable
    group by SearchTerm
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    thanks, works perfect, i had an identical query but without the "GROUP BY" clause, i guess you need that for it to work

  4. #4
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    I've tried to expand on the query in another area of the site but running into a few problems,

    - tblSections (ID, Name, Description)

    - tblSectionsLog (ID, SectionID, DateVisited)

    tblSectionsLog is identical to the table in the above post, but it holds a row for each time a section of the site has been visited. but as well as counting how many times a section has been visited, I need to pull out it's Name and Description.

    Any ideas? Thanks

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to GROUP BY both Name and Description.

    Look up use of the GROUP BY clause in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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