SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast COMpWiZkEv's Avatar
    Join Date
    Jul 2002
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple MySQL Query

    Is there any way to get the same effect from a MySQL query as the PHP code shown below. I want to total all of the numbers in a hits column.

    PHP Code:
    $i '0';
    $result mysql_query('SELECT hits FROM pages');
    while(
    $row mysql_fetch_array($result)) {
         
    $i $i $row['hits'];
    }
    echo(
    $i ' hits'); 

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2003
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT count(hits) FROM PAGES

    will give you the total number of rows

    SELECT sum(hits) FROM PAGES

    will give you the sum of the values of all rows.

    So I guess the second one is the one you're looking for.

  3. #3
    SitePoint Zealot Egghead's Avatar
    Join Date
    Feb 2002
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another handy tip:
    If you want to use the query on all columns in the table use this...
    SELECT count(*) FROM PAGES

  4. #4
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm unclear what that accomplishes Egghead. You still only get a count of rows, you can't access specific fields values. And you can't use SUM(*). At least it gives an error when I try testing on a table with three INT fields, and two records...
    $slider = 'n00b';

  5. #5
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now, SUM(field1 + field2 + ... + fieldn) does work, as long as those fields are numerical.
    $slider = 'n00b';

  6. #6
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    One small aside ... not sure about the SUM() command but certainly with the COUNT() command it is important that there is no space between COUNT and ( ... i.e.

    Code:
    SELECT COUNT(*) FROM Table ... works fine
    
    SELECT COUNT (*) FROM Table ... will throw an error.
    Ian Anderson
    www.siteguru.co.uk

  7. #7
    SitePoint Zealot Egghead's Avatar
    Join Date
    Feb 2002
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by slider
    I'm unclear what that accomplishes Egghead. You still only get a count of rows, you can't access specific fields values.
    That's exactly what it is for - counting the number of rows. It saves you having to put all the column names into the query that's all. It becomes useful when you use it in conjunction with a WHERE clause.
    Example: SELECT count(*) FROM pages WHERE grade > '10'

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I think what slider was confused about was exactly how your suggestion helped in relation to the original question.
    I want to total all of the numbers in a hits column
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

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