#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3

    Count amount of rows with value n


    Hi all,

    First I want to show you the table structure with some content.

    -moods-
    id*****name*****moodId*****mood
    -------------------------------------------------
    1*****math *****1 *****happy
    2*****math *****1 *****happy
    3*****math *****2 *****sad

    My problem is I want to COUNT the different values of mood, so that I get
    2 - happy
    1 - sad

    But I was thinking there must be another way than creating a new query per value in moodId.

    SELECT COUNT(moodId) from moods where moodId=1 ORDER BY mood ASC

    SELECT COUNT(moodId) from moods where moodId=2 ORDER BY mood ASC

    and so on...

    My goal in the end is to take the COUNT(*) from the table and calculate the percentage of each mood so that in the end I can produce this,

    happy - 66.66%
    sad - 33.33%

    Could it be there was a built in fuction to do so?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by odp
    Could it be there was a built in fuction to do so?
    yes, although it's not a function, it's a basic part of sql called GROUP BY
    Code:
    SELECT mood
         , COUNT(*) as mood_count
         , 100.0 * COUNT(*) /
           ( SELECT COUNT(*)
               FROM moods ) AS mood_percent 
      FROM moods 
    GROUP
        BY mood
    ORDER 
        BY mood ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by r937
    yes, although it's not a function, it's a basic part of sql called GROUP BY
    Code:
    SELECT mood
         , COUNT(*) as mood_count
         , 100.0 * COUNT(*) /
           ( SELECT COUNT(*)
               FROM moods ) AS mood_percent 
      FROM moods 
    GROUP
        BY mood
    ORDER 
        BY mood ASC
    That worked wonders, thanks a lot.

    I am also trying to chain this result to another query but without success.
    So as of now my query looks like thus

    Code:
    SELECT course_has_mood.mood_idmood
         , COUNT(*) as mood_count
         , 100.0 * COUNT(*) /
           ( SELECT COUNT(*)
               FROM course_has_mood ) AS mood_percent 
      FROM course_has_mood 
    GROUP
        BY mood__idmood
    ORDER 
        BY mood_percent DESC
    LIMIT 1
    Which returns one row as it should, but I need this query in another scenario.
    Basically I want to query a building where the is one or more courses. Based on the mood of those couses I want to run the query and get the collected mood score in percentage.

    The thing is I dont want to create a new table buildings_has_mood populated the same way I populated course_has_mood. I want to query the building table, and building_has_course table to get the course where courseid and buildingid has a match. Like so,

    Code:
    SELECT * FROM 
        buildings, course_has_buildings, course 
    WHERE 
        buildings.idbuilding='1' 
    AND 
        course_has_buildings.buildings_idbuilding='1' 
    AND 
        course_has_buildings.course_idcourse=course.idcourse
    This query will return one row,
    idbuilding,idcourse,

    So my question is how do I chain the result of this query to the query you provided so my result will be
    idbuilding, idcourse, mood_idmood, mood_count, mood_percent
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    if you want that type of help, you will have to explain the purpose and contents of all your tables

    until today, i had no idea that buildings have moods
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by r937
    if you want that type of help, you will have to explain the purpose and contents of all your tables

    until today, i had no idea that buildings have moods
    HAHAHA no buildings dont have moods, I surtainly dont hope so.

    but since your query gives me the mood in percents in a given course. It could be interesting to see the accumulated top mood in a building that holds more courses.

    Would you like so see a dump of the three tables?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by odp
    HAHAHA no buildings dont have moods, I surtainly dont hope so.

    but since your query gives me the mood in percents in a given course. It could be interesting to see the accumulated top mood in a building that holds more courses.

    Would you like so see a dump of the three tables?
    Code:
    mysql> desc course;
    +-------------+-------------+------+-----+---------+-------+
    | Field       | Type        | Null | Key | Default | Extra |
    +-------------+-------------+------+-----+---------+-------+
    | idcourse    | int(11)     | NO   | PRI | NULL    |       |
    | name        | varchar(45) | YES  |     | NULL    |       |
    | description | text        | YES  |     | NULL    |       |
    +-------------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    mysql> desc mood;
    +--------+--------------+------+-----+---------+-------+
    | Field  | Type         | Null | Key | Default | Extra |
    +--------+--------------+------+-----+---------+-------+
    | idmood | int(11)      | NO   | PRI | NULL    |       |
    | mood   | varchar(45)  | YES  |     | NULL    |       |
    | image  | varchar(100) | YES  |     | NULL    |       |
    +--------+--------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    mysql> desc course_has_mood;
    +-----------------+-----------+------+-----+-------------------+-----------------------------+
    | Field           | Type      | Null | Key | Default           | Extra                       |
    +-----------------+-----------+------+-----+-------------------+-----------------------------+
    | course_idcourse | int(11)   | NO   | PRI | NULL              | auto_increment              |
    | mood_idmood     | int(11)   | NO   | PRI | NULL              |                             |
    | users_idusers   | int(11)   | NO   | PRI | NULL              |                             |
    | timestamp       | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +-----------------+-----------+------+-----+-------------------+-----------------------------+
    4 rows in set (0.00 sec)
    
    mysql> desc building;
    ERROR 1146 (42S02): Table 'littera.building' doesn't exist
    mysql> desc buildings;
    +-----------------+-------------+------+-----+---------+----------------+
    | Field           | Type        | Null | Key | Default | Extra          |
    +-----------------+-------------+------+-----+---------+----------------+
    | idbuilding      | int(11)     | NO   | PRI | NULL    | auto_increment |
    | buildingname    | varchar(45) | YES  |     | NULL    |                |
    | buildingaddress | varchar(45) | YES  |     | NULL    |                |
    +-----------------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> desc course_has_buildings;
    +----------------------+---------+------+-----+---------+-------+
    | Field                | Type    | Null | Key | Default | Extra |
    +----------------------+---------+------+-----+---------+-------+
    | course_idcourse      | int(11) | NO   | PRI | NULL    |       |
    | buildings_idbuilding | int(11) | NO   | PRI | NULL    |       |
    +----------------------+---------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by odp
    Basically I want to query a building where the is one or more courses.
    that means an INNER JOIN...
    Code:
      FROM buildings 
    INNER
      JOIN course_has_buildings
        ON course_has_buildings.buildings_idbuilding = buildings.idbuilding
    Originally Posted by odp
    Based on the mood of those couses ...
    so we also need to join course_has_mood
    Code:
      FROM buildings 
    INNER
      JOIN course_has_buildings
        ON course_has_buildings.buildings_idbuilding = buildings.idbuilding 
    INNER
      JOIN course_has_mood
        ON course_has_mood.course_idcourse = course_has_buildings.course_idcourse
    Originally Posted by odp
    ...I want to run the query and get the collected mood score in percentage.
    okay, here i got lost

    when you say the "collected" mood score, do you mean across all buildings, or score per building?

    this will affect the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    77
    Rep Power
    3
    Originally Posted by r937
    that means an INNER JOIN...
    Code:
      FROM buildings 
    INNER
      JOIN course_has_buildings
        ON course_has_buildings.buildings_idbuilding = buildings.idbuilding
    so we also need to join course_has_mood
    Code:
      FROM buildings 
    INNER
      JOIN course_has_buildings
        ON course_has_buildings.buildings_idbuilding = buildings.idbuilding 
    INNER
      JOIN course_has_mood
        ON course_has_mood.course_idcourse = course_has_buildings.course_idcourse

    okay, here i got lost

    when you say the "collected" mood score, do you mean across all buildings, or score per building?

    this will affect the GROUP BY clause
    I mean score pr. building. so a joined score from all the courses in one building.

IMN logo majestic logo threadwatch logo seochat tools logo