CODEWITHSUNDEEP
mysqlgroup-bywhere-clause
user7400789
user7400789

Reputation:

Where and group conflict?

I have a table like this:

mysql> select * from studentscore;
+------------+-----------+-------+
| student_id | cource_id | score |
+------------+-----------+-------+
|          1 |         1 |    80 |
|          1 |         2 |    90 |
|          1 |         3 |    85 |
|          1 |         4 |    78 |
|          2 |         2 |    53 |
|          2 |         3 |    77 |
|          2 |         5 |    80 |
|          3 |         1 |    71 |
|          3 |         2 |    70 |
|          3 |         4 |    80 |
|          3 |         5 |    65 |
|          3 |         6 |    75 |
|          4 |         2 |    90 |
|          4 |         3 |    80 |
|          4 |         4 |    70 |
|          4 |         6 |    95 |
|          5 |         1 |    60 |
|          5 |         2 |    70 |
|          5 |         5 |    80 |
|          5 |         6 |    69 |
|          6 |         1 |    76 |
|          6 |         2 |    88 |
|          6 |         3 |    87 |
|          7 |         4 |    80 |
|          8 |         2 |    71 |
|          8 |         3 |    58 |
|          8 |         5 |    68 |
|          9 |         2 |    88 |
|         10 |         1 |    77 |
|         10 |         2 |    76 |
|         10 |         3 |    80 |
|         10 |         4 |    85 |
|         10 |         5 |    83 |
|         11 |         3 |    80 |
|         12 |         4 |    99 |
|         13 |         5 |    74 |
+------------+-----------+-------+

I want to show student_id and students' average scores that are higher than 80.

The output I want is like this:

+------------+-------------------+
| student_id | Average           |
+------------+-------------------+
|          1 |             83.25 |
|          4 |             83.75 |
|          6 | 83.66666666666667 | // and how can I make this result shorter like 83.67?
|          7 |                80 |
|          9 |                88 |
|         10 |              80.2 |
|         11 |                80 |
|         12 |                99 |
+------------+-------------------+

I've tried the following codes 

mysql> select   student_id, avg(score) as average_score
    -> from     studentscore
    -> group by student_id
    -> where    avg(score) >= 80;

and it gave me an syntax error.

I know by rules the where clause should go before the group by clause but I can't because the where clause depends on the result from the group by clause, and if I switch their position it will give me another error("Invalid use of group function").

Can some one tell me how to get the table I want?

Upvotes: 0

Views: 72

Answers (4)

Luigi Dallavalle
Luigi Dallavalle

Reputation: 320

use having instead of where. Here's the difference:

  1. with where you can write a predicate that will be applied to each row
  2. with having you can write a predicate that will applied to each group

and in your case, the 2nd is the only solution that can work.

Upvotes: 1

trueman
trueman

Reputation: 1

where applies a filter to your data before grouping has taken place, whereas having applies a filter post-grouping. round(,2) will format as you also ask:

select student_id, round(avg(score), 2) as average_score
from studentscore
group by student_id
having average_score >= 80;

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

select   student_id, avg(score) as average_score
     from     studentscore
     group by student_id
     having avg(score) >= 80;

Upvotes: 0

coenni
coenni

Reputation: 422

use "having" instead of "where"

Upvotes: 3

Related Questions