Trying to find values that are greater than the average. SQL Developer from ORACLE
I have a table of words that come up in a table of series. I am trying to find which series have more words used (unique id's) than the average of all the words used in all the series. So far I've got this code.
POSTING contains the primary key IDT which is the unique word ID and the foreign key IDS which is the SERIES primary key.
SELECT s.NAME, COUNT(UNIQUE p.IDT) as vocSize FROM POSTING p, SERIES s WHERE s.IDS = p.IDS AND (SELECT AVG(IDT) FROM POSTING) < p.IDT;
You can do what you want using window functions and aggregations. If I understand correctly, the query looks like this:
select * from (select s.name, count(distinct p.idt) as vocsize, avg(count(distinct p.idt)) over () as avg_vocsize from series s join posting p on s.ids = p.ids group by s.name ) s where vocsize > avg_vocsize;
Without sample data, I'm somewhat guessing, but the fact you were trying to do unique within the count makes me believe you need two grouping elements.
SELECT s.NAME, p.IDT, COUNT(*) as vocSize FROM POSTING p JOIN SERIES s ON (s.IDS = p.IDS) GROUP BY s.NAME, p.IDT HAVING COUNT(UNIQUE p.IDT) > (SELECT AVG(IDT) FROM POSTING pp WHERE p.NAME = pp.NAME);