Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: COUNT row but exclude 0

    I have a table called 'userrating' with 3 INT fields in it as shown with sample data:

    rating
    0
    3

    rating2
    2
    4

    rating3
    0
    0

    I want it to count each row of each field in seperate queries BUT ONLY if there is not a '0' in that row.

    So the count of:
    rating = 1
    rating2 = 2
    rating3 = 0

    I have a start put i don't know how to check for zeros in the rows, here is what i have for the first field rating:

    $ratinginfos = $db->query_read("SELECT *, COUNT(*) AS totalvotes, SUM(rating) AS total FROM " . TABLE_PREFIX . "userrating WHERE userid = $userinfo[userid] AND active=1 GROUP BY userid ORDER BY dateline DESC");


    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query that you have shown doesn't look anything like the ratings tables

    also, you cannot "select star" at the same time as using a GROUP BY

    if you don't want to count zeros, here's the way you do it --
    Code:
    select sum(case when foo = 0 then 0 else 1 end) as non_zero_counts from ...
    Last edited by r937; 03-10-06 at 19:40.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    Quote Originally Posted by r937
    the query that you have shown doesn't look anything like the ratings tables

    also, you cannot "select star" at the same time as using a GROUP BY

    if you don't want to count zeros, here's the way you do it --
    Code:
    select sum(case when foo = 0 then 0 else 1 end) as non_zero_counts from ...
    Thanks for the relp, actually the code i pasted came straight from a mod i was using, and does work. But maybe I just don't know what it does? Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the code works, you are lucky, it's pretty bad sql (no offence to you, it's mysql's fault for allowing such awful constructions)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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