Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    25

    Unanswered: Return count even when no values

    So I have the query below which gets the count of each route_grade for a person.

    Code:
    SELECT
    COUNT(*) AS 'count', 
    r.`route_grade`
    FROM `mc_routes` r, `mc_routeclimbs` c, `mc_areas` a, `mc_countries` co
    WHERE c.`route_id` =  r.`route_id`
    AND r.`area_id` = a.`area_id`
    AND a.`country_id` = co.`country_id`
    AND c.`user_id` = :user_id
    GROUP BY r.`route_grade`
    ORDER BY r.`route_grade` DESC

    The route_grade is an enum field in the mc_routes table:
    Code:
    `route_grade` enum('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+','7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b','9b+') NOT NULL,
    So for a given person the results might look like:
    Code:
    5b   12
    6c   8
    7a   3
    So he climbed 12 routes graded 5b, 8 routes graded 6c and 3 routes graded 7a. However, what I want is to also have results for the grades with no results. Meaning:
    Code:
    3    0
    4    0
    5a   0
    .. etc
    5b   12
    5c   0
    .. etc
    I could do this with some PHP logic, but I'm sure there's a way to do it in the mysql query. Any ideas?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need to create a separate table with the route grades and use a left outer join to get this working. Here is a example:

    Code:
    mysql> select * from grades;
    +----+-------+
    | id | grade |
    +----+-------+
    |  1 | A     | 
    |  2 | B     | 
    |  3 | C     | 
    |  4 | D     | 
    +----+-------+
    4 rows in set (0.00 sec)
    
    mysql> select * from usergrades;
    +----+-------+---------+
    | id | user  | gradeid |
    +----+-------+---------+
    |  1 | Tom   |       1 | 
    |  2 | Tom   |       2 | 
    |  3 | Dick  |       3 | 
    |  4 | Dick  |       1 | 
    |  5 | Harry |       4 | 
    |  6 | Tom   |       2 | 
    |  7 | Harry |       4 | 
    +----+-------+---------+
    7 rows in set (0.00 sec)
    
    mysql> select a.grade, count(gradeid) from grades a left outer join usergrades b on (a.id = b.gradeid and b.user = 'Tom') group by a.grade;
    +-------+----------------+
    | grade | count(gradeid) |
    +-------+----------------+
    | A     |              1 | 
    | B     |              2 | 
    | C     |              0 | 
    | D     |              0 | 
    +-------+----------------+
    4 rows in set (0.00 sec)
    
    mysql> select a.grade, count(gradeid) from grades a left outer join usergrades b on (a.id = b.gradeid and b.user = 'Harry') group by a.grade;
    +-------+----------------+
    | grade | count(gradeid) |
    +-------+----------------+
    | A     |              0 | 
    | B     |              0 | 
    | C     |              0 | 
    | D     |              2 | 
    +-------+----------------+
    4 rows in set (0.00 sec)
    
    mysql> select a.grade, count(gradeid) from grades a left outer join usergrades b on (a.id = b.gradeid and b.user = 'Dick') group by a.grade;
    +-------+----------------+
    | grade | count(gradeid) |
    +-------+----------------+
    | A     |              1 | 
    | B     |              0 | 
    | C     |              1 | 
    | D     |              0 | 
    +-------+----------------+
    4 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2008
    Posts
    25
    Thanks a lot for your reply. It had crossed my mind that with separate tables it would be possible, but I wondered if it would also be possible even with the grades in the same table. If that's not possible and/or a bad solution, I'll have to extract the existing data out into a separate table.

  4. #4
    Join Date
    Aug 2008
    Posts
    25
    As a follow up question. When I put the grades in their own table, is it necessary to create a primary key column id? Or could I just create the table with one column 'grade'?

Posting Permissions

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