CODEWITHSUNDEEP
mysqlsql-server
Jackman10k
Jackman10k

Reputation: 21

How do I get an average of one column based on the value of another column?

SQL Question

I am currently trying to get the average of a certain subset of a column based on the shared value of another column. Here's some example data:

Score     Player
  5          1
  9          1
  7          1
  8          2
  3          2
  6          2

So how can I get the average of Player 1's scores separately from Player 2's? My first thought is to try something like:

SELECT AVG(score) FROM table WHERE DISTINCT player

But, of course, distinct only works in the select portion of the query. Any attempt I've made to use a subquery results in an error saying:

the subquery returns more than one row.

Any assistance anyone could give would be greatly appreciated.

Upvotes: 2

Views: 8983

Answers (2)

Faisal
Faisal

Reputation: 4765

Try this below query. I hope you are looking for this solution.

LIVE DEMO

SELECT
    Player,
    AVG(Score) AS Avarage
FROM
    table_name
GROUP BY
    Player

Output:

enter image description here

Upvotes: 2

shmosel
shmosel

Reputation: 50716

Sounds like you're looking for GROUP BY:

SELECT Player, AVG(Score)
  FROM my_table
 GROUP BY Player;

Upvotes: 5

Related Questions