1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SELECT all rows in a group where A1>AVG(A1)

Discussion in 'SQL PL/SQL' started by Paul, Apr 7, 2014.

  1. Paul

    Paul Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    SELECT all rows in a group where A1>AVG(A1)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What key of aggregation ?



    if are necessary an output of all columns of table , then it is better to use analytics

    Code (SQL):

    DROP TABLE testa purge;
    CREATE TABLE testa
    ( id INT PRIMARY KEY,
      a1 NUMBER(*,4)
    );
    INSERT INTO testa
    SELECT
          level id,
          round(dbms_random.VALUE (1,level),4) A1
    FROM dual
    CONNECT BY level < 11;
    commit;

    WITH
    preres AS
    (
    SELECT
          id ,
          a1,
          avg(a1) OVER() av_a1
    FROM  testa
    )
     
    SELECT * FROM preres
    WHERE a1>av_a1;


    SELECT
          t.id ,
          t.a1,
          av_a1.av
    FROM  testa t ,(SELECT avg(a1) av FROM testa ) av_a1
    WHERE a1 > av_a1.av

    DROP TABLE testa purge;
    CREATE TABLE testa
    ( id INT PRIMARY KEY,
      a1 NUMBER(*,4)
    );
    INSERT INTO testa
    SELECT
          level id,
          round(dbms_random.VALUE (1,level),4) A1
    FROM dual
    CONNECT BY level < 11;
    commit;

    WITH
    preres AS
    (
    SELECT
          id ,
          a1,
          avg(a1) OVER() av_a1
    FROM  testa
    )
     
    SELECT * FROM preres
    WHERE a1>av_a1;

    SELECT
          t.id ,
          t.a1,
          av_a1.av
    FROM  testa t ,(SELECT avg(a1) av FROM testa ) av_a1
    WHERE a1 > av_a1.av;



    SQL>
     
    TABLE dropped
     
    TABLE created
     
    10 ROWS inserted
     
    Commit complete
     
                                         ID                                       A1      AV_A1
    --------------------------------------- ---------------------------------------- ----------
                                          5                                   4.0718    2.94974
                                          6                                   5.9706    2.94974
                                          9                                   3.1248    2.94974
                                         10                                   5.6901    2.94974
     
                                         ID                                       A1         AV
    --------------------------------------- ---------------------------------------- ----------
                                          5                                   4.0718    2.94974
                                          6                                   5.9706    2.94974
                                          9                                   3.1248    2.94974
                                         10                                   5.6901    2.94974
     
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are a couple of ways to do this, but it would be nice had you posted WHAT you want the average of and what data you're actually seeking. Here is an example that might help you:


    Code (SQL):

     
    SQL> CREATE TABLE lotsa_stuff(
      2        stuff_Id      NUMBER NOT NULL,
      3        stuff_name      varchar2(40),
      4        stuff_code      NUMBER,
      5        stuff_value     NUMBER);


    TABLE created.


    SQL>
    SQL> BEGIN
      2        FOR i IN 1..10000 loop
      3         INSERT INTO lotsa_stuff
      4         VALUES(i, 'Stuff and such '||i, MOD(i,43)+1, MOD(i,19)*1000);
      5        END loop;
      6  
      7        commit;
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT ls.*
      2  FROM lotsa_stuff ls,
      3  (SELECT avg(stuff_value) sva FROM lotsa_stuff) a
      4  WHERE ls.stuff_value > a.sva;


      STUFF_ID STUFF_NAME                               STUFF_CODE STUFF_VALUE
    ---------- ---------------------------------------- ---------- -----------
           851 Stuff AND such 851                               35       15000
           852 Stuff AND such 852                               36       16000
           853 Stuff AND such 853                               37       17000
           854 Stuff AND such 854                               38       18000
           864 Stuff AND such 864                                5        9000
           865 Stuff AND such 865                                6       10000
           866 Stuff AND such 866                                7       11000
           867 Stuff AND such 867                                8       12000
           868 Stuff AND such 868                                9       13000
           869 Stuff AND such 869                               10       14000
           870 Stuff AND such 870                               11       15000
    ...
          9661 Stuff AND such 9661                              30        9000
          9662 Stuff AND such 9662                              31       10000
          9663 Stuff AND such 9663                              32       11000
          9664 Stuff AND such 9664                              33       12000
          9665 Stuff AND such 9665                              34       13000
          9666 Stuff AND such 9666                              35       14000
          9667 Stuff AND such 9667                              36       15000
          9668 Stuff AND such 9668                              37       16000
          9669 Stuff AND such 9669                              38       17000


    5260 ROWS selected.


    SQL>
    SQL> SELECT *
      2  FROM lotsa_stuff
      3  WHERE stuff_value > (SELECT avg(stuff_value) FROM lotsa_stuff);


      STUFF_ID STUFF_NAME                               STUFF_CODE STUFF_VALUE
    ---------- ---------------------------------------- ---------- -----------
           851 Stuff AND such 851                               35       15000
           852 Stuff AND such 852                               36       16000
           853 Stuff AND such 853                               37       17000
           854 Stuff AND such 854                               38       18000
           864 Stuff AND such 864                                5        9000
           865 Stuff AND such 865                                6       10000
           866 Stuff AND such 866                                7       11000
           867 Stuff AND such 867                                8       12000
           868 Stuff AND such 868                                9       13000
           869 Stuff AND such 869                               10       14000
           870 Stuff AND such 870                               11       15000
           871 Stuff AND such 871                               12       16000
           872 Stuff AND such 872                               13       17000
           873 Stuff AND such 873                               14       18000
           883 Stuff AND such 883                               24        9000
           884 Stuff AND such 884                               25       10000
           885 Stuff AND such 885                               26       11000
           886 Stuff AND such 886                               27       12000
           887 Stuff AND such 887                               28       13000
           888 Stuff AND such 888                               29       14000
           889 Stuff AND such 889                               30       15000
           890 Stuff AND such 890                               31       16000
    ...
          9627 Stuff AND such 9627                              39       13000
          9628 Stuff AND such 9628                              40       14000
          9629 Stuff AND such 9629                              41       15000
          9630 Stuff AND such 9630                              42       16000
          9631 Stuff AND such 9631                              43       17000
          9632 Stuff AND such 9632                               1       18000
          9642 Stuff AND such 9642                              11        9000
          9643 Stuff AND such 9643                              12       10000
          9644 Stuff AND such 9644                              13       11000
          9645 Stuff AND such 9645                              14       12000
          9646 Stuff AND such 9646                              15       13000
          9647 Stuff AND such 9647                              16       14000
          9648 Stuff AND such 9648                              17       15000
          9649 Stuff AND such 9649                              18       16000
          9650 Stuff AND such 9650                              19       17000
          9651 Stuff AND such 9651                              20       18000
          9661 Stuff AND such 9661                              30        9000
          9662 Stuff AND such 9662                              31       10000
          9663 Stuff AND such 9663                              32       11000
          9664 Stuff AND such 9664                              33       12000
          9665 Stuff AND such 9665                              34       13000
          9666 Stuff AND such 9666                              35       14000
          9667 Stuff AND such 9667                              36       15000
          9668 Stuff AND such 9668                              37       16000
          9669 Stuff AND such 9669                              38       17000


    5260 ROWS selected.


    SQL>