sql distinct selection avg aggregate

here is my table schema

SessionID and relevancy is not unique

i want to remove urlid duplicates and have an avg of relevancy values corresponding of each unique urlid.

this is my code

select distinct URLID,avg (Relevancy) from ccc 
group by URLID,Relevancy

​x
 
select distinct URLID,avg (Relevancy) from ccc group by URLID,Relevancy​

my result

for example i want to have this for urlid=15030753 (2+0)/2=1

urlid         avg
 ...          ...
15030753      1
...           ...

 
urlid         avg ...          ...15030753      1...           ...​

Answer

Drop the Relevancy in the GROUP BY clause, if you want to aggregate over it. You can also drop the DISTINCT as there will be just one URLID entry due to the GROUP BY.

select URLID,avg (Relevancy) from ccc 
group by URLID

 
select URLID,avg (Relevancy) from ccc group by URLID​

Advertisement

Answer