CODEWITHSUNDEEP
sqloracle-database
Lemoncide
Lemoncide

Reputation: 79

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;

Upvotes: 0

Views: 572

Answers (2)

Scott Sosna
Scott Sosna

Reputation: 1413

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);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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;

Upvotes: 3

Related Questions