CODEWITHSUNDEEP
mysql
Dino
Dino

Reputation: 561

MySQL Count(Distinct) returning one value

I have the following table below and I am trying to find each IID that has at least 2 or more same IID's and find the average of the stats. The MySQL statement is as follows: select count(distinct IID) as counted from rate; but I get one field and it states a number of 15, the table is very small and only has 20 tuples. I am stuck and can't go any further than this. 

UID| IID   | stats
-----------------
1  | 1     | 3
1  | 1     | 4
1  | 3     | 1
2  | 3     | 1
2  | 3     | 1
2  | 1     | 3
2  | 2     | 4

The result I would like to see is grouped by IID's if there is two or more and an average of stats. I have a feeling I have to group by IID and sum the stats and divide by amount of count. 

IID   | stats
-----------------
1     | 3.5
3     | 1.5

Upvotes: 0

Views: 201

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

The following query will return the average of stats for each iid that has more than one row:

select iid, avg(stats)
from table t
group by iid
having count(*) > 1;

This seems like a reasonable result, but the values would be:

1    3.33
3    1

I am not sure what calculation you have in mind for the results in your question.

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

If you really want an average then just use avg(stats). I couldn't see how to make the numbers match with your output so I thought perhaps you wanted a different divisor.

select sum(stats) / (count(distinct UID) * 1e) /* cast to float */
from rate
group by IID
having count(*) > 1

Upvotes: 1

kid
kid

Reputation: 153

why don't you use group_by(IID) that will group your data according to IID then use where clause in it.

Upvotes: 0

mrks
mrks

Reputation: 8333

That is most likely correct. Your statement counts the number of distinct values in that column. For your case, you should use group by and having.

Upvotes: 0

Related Questions