CODEWITHSUNDEEP
javascriptmysql
Milos
Milos

Reputation: 1263

How do i build a query that averages scores in a db based on another column

Right now I have a table "uniqueids" that looks like:

+----+-----------------+------+-------+------------+
| id | uniqueid        | name | score | date       |
+----+-----------------+------+-------+------------+
|  1 | bob1            | bob  |     4 | 02-10 |
|  2 | bob2            | bob  |    49 | 02-15 |
|  3 | bob3            | bob  |    48 | 03-01 |
|  4 | cari1           | cari |    76 | 02-15 |
|  5 | cari2           | cari |    60 | 02-16 |
|  6 | cari3           | cari |    71 | 03-05 |
|  7 | john1           | john |    54 | 01-05 |
|  8 | john2           | john |    56 | 02-13 |
|  9 | john3           | john |    53 | 03-13 |
+----+-----------------+------+-------+------------+

What i want is to take the average score for each name. So in the end I'd get something along the lines of:

+----+---------
|name  | score |
+----+---------
|bob   |  33.6 |
|cari  |  69   |
|john  |  54.3 |
---------------

There could be more names added to the database later, but for now I've been focusing on just doing this for one name. My progress on one name is this:

'SELECT * from uniqueids where name='bob' and SELECT AVG(score)' but this doesn't work for me, it just mentions I have an error in my syntax. Even if it did, it would only do it for bob, whereas I'd need it done for each name.

Any ideas on what I can do to get this query to where I need it to be?

Upvotes: 0

Views: 39

Answers (2)

Umer
Umer

Reputation: 142

select name, avg(score) Score from uniqueids group by name; this would help you get the desired answer.

Upvotes: 0

jpw
jpw

Reputation: 44911

This is a pretty standard SQL query. You just need to combine the avg function with a suitable group by clause (and with the where clause if you want to limit the result to a specific name).

select name, avg(score) as "average score"
from uniqueids 
where name = 'bob' -- remove this line if you want averages for all names
group by name

Sample SQL Fiddle

Upvotes: 2

Related Questions