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;

Answers


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

Need Your Help

No Method Error Undefined method 'save' for nil:NilClass

ruby-on-rails model-view-controller methods null

I'm getting this error when i try to create a "Lecture" via my Lecture controller's create method. This used to work but i went on to work on other parts of the app &amp; then of course i come bac...

The Report Viewer Web Control requires a System.Web.UI.ScriptManager on the web form

.net reporting-services rdl

I have tried to call a .rdl report created in SSRS to be called in an aspx page; but I got the following error: