CODEWITHSUNDEEP
phpsql
Rayyan
Rayyan

Reputation: 1

how to select data that have the same value with sql?

I have a table with three columns, id, name, and value, as shown below. I want to count the average where the id and value columns are the same, how can I do this?

+----+--------+-------+
| id |  name  | value |
+----+--------+-------+
| 2  | rahmat |   3   |
| 2  | olive  |   5   |
| 3  | sari   |   3   |
| 3  | ryan   |   2   |
| 1  | zaki   |   1   |
+----+--------+-------+

Upvotes: 0

Views: 54

Answers (2)

Shaikh Muhammad Fahad
Shaikh Muhammad Fahad

Reputation: 33

according to your question yes you need to use 

SELECT AVG(value)
FROM #table
WHERE id = value

I have created a sqlfiddle here http://sqlfiddle.com/#!3/9eecb7/4105

from the nature of this question I feel you trying to calculate average of values of those rows having same ids. If that's the case I have created another fiddle http://sqlfiddle.com/#!3/9eecb7/4110 where you need to use group by

select id, sum(value)/count(id) as average from #table group by id

Lemme know if it is something you are after or you need something else.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Try using this query:

SELECT AVG(value)
FROM table
WHERE id = value

The output from the sample table you gave in your OP would be 1.5, since sari and zaki are the only 2 users whose records have id and value columns which are equal.

Upvotes: 1

Related Questions