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!

Help with query, beginner :)

Discussion in 'SQL PL/SQL' started by Kolins, Aug 14, 2013.

  1. Kolins

    Kolins Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello everyone, im newbie at sql because i started learning it just a few days ago, and i already need help :)

    Im using Oracle Database 11g Express Edition and the demo tables for practicing that are included there (employees, departments, jobs, etc..). Im having trouble with GROUP BY command, so i could use some help on this for example: I want to know who gets top salary in each of the departments ? So i need fist_name, last_name, departments_id, max(salary) and i should group by that by departments_id... ?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I'm assuming you are looking for something along these lines...

    select a, b, max(c)
    from table
    group by a, b
     
  3. krishprince

    krishprince Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    100
    hello,
    From your Question what i am assuming is when you run the sql query with group by department you would be facing 979 not a group by expression

    this is because the oracle doesnt allow only one column to be grouped.

    the query you need to use is
    select ename,sal,deptno from emp where sal in(select max(sal) from
    emp group by deptno);

    thankyou
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "hello,
    From your Question what i am assuming is when you run the sql query with group by department you would be facing 979 not a group by expression

    this is because the oracle doesnt allow only one column to be grouped."

    That is simply not true:

    Code (SQL):
    SQL> SELECT deptno, SUM(sal), avg(sal)
      2  FROM emp
      3  GROUP BY deptno;
     
        DEPTNO   SUM(SAL)   AVG(SAL)
    ---------- ---------- ----------
            30       9400 1566.66667
            20      10875       2175
            10       8750 2916.66667
     
    SQL>
    I understand what you're trying to say which is this:

    Code (SQL):
    SQL> SELECT deptno, job, SUM(sal), avg(sal)
      2  FROM emp
      3  GROUP BY deptno;
    SELECT deptno, job, SUM(sal), avg(sal)
                   *
    ERROR at line 1:
    ORA-00979: NOT a GROUP BY expression

    SQL>
    SQL> SELECT deptno, job, SUM(sal), avg(sal)
      2  FROM emp
      3  GROUP BY deptno, job;
     
        DEPTNO JOB         SUM(SAL)   AVG(SAL)
    ---------- --------- ---------- ----------
            20 CLERK           1900        950
            30 SALESMAN        5600       1400
            20 MANAGER         2975       2975
            30 CLERK            950        950
            10 PRESIDENT       5000       5000
            30 MANAGER         2850       2850
            10 CLERK           1300       1300
            10 MANAGER         2450       2450
            20 ANALYST         6000       3000
     
    9 ROWS selected.
     
    SQL>
    The way you stated it is incorrect as it's entirely possible, with the correct query, to group by one column.
     
  5. krishprince

    krishprince Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    100
    hello sir,
    yes you are correct ,the way i said was wrong and as already DTSIGuy given i didnt given that answer .
    thankyou.
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Well I'm glad THAT got cleared up....I was having a WTF moment. ;-)
     
  7. Kolins

    Kolins Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55



    That's it :)

    Thanks everyone!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Let's explain this properly.

    For a group by query to be correctly written you must group by all non-aggregate columns in the select list, whether there is one column or two dozen. Looking at a working example:

    Code (SQL):
    SQL> CREATE TABLE inheritance(
      2          coin    varchar2(12) NOT NULL,
      3          rolls   NUMBER  NOT NULL
      4  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 2..70000 loop
      3                  IF MOD(i,2) = 0 THEN
      4                          INSERT INTO inheritance
      5                          VALUES ('Nickel', 1);
      6                  elsif MOD(i,3) = 0 THEN
      7                          INSERT INTO inheritance
      8                          VALUES ('Quarter', 1);
      9                  elsif MOD(i,5) = 0 THEN
     10                          INSERT INTO inheritance
     11                          VALUES ('Dime', 1);
     12                  END IF;
     13
     14          END loop;
     15
     16          commit;
     17
     18  END;
     19  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT coin, SUM(rolls)
      2  FROM inheritance
      3  GROUP BY coin;
     
    COIN         SUM(ROLLS)
    ------------ ----------
    Quarter           11667
    Dime               4667
    Nickel            35000

    SQL>
    SQL> SELECT coin, COUNT(*)
      2  FROM inheritance
      3  GROUP BY coin;
     
    COIN           COUNT(*)
    ------------ ----------
    Quarter           11667
    Dime               4667
    Nickel            35000

    SQL>
    SQL> break ON report skip 1
    SQL> compute SUM OF dollars ON report
    SQL>
    SQL> SELECT coin,
      2          CASE WHEN coin = 'Dime' THEN ct * 5
      3          WHEN coin = 'Quarter' THEN ct * 10
      4          WHEN coin = 'Nickel' THEN ct * 2
      5          END dollars
      6  FROM
      7  (SELECT coin, COUNT(*) ct
      8  FROM inheritance
      9  GROUP BY coin);
     
    COIN            DOLLARS
    ------------ ----------
    Quarter          116670
    Dime              23335
    Nickel            70000
                 ----------
    SUM              210005

    SQL>
    SQL> ALTER TABLE inheritance ADD roll_val NUMBER;
     
    TABLE altered.
     
    SQL>
    SQL> UPDATE inheritance i
      2  SET i.roll_val = (SELECT DISTINCT
      3          CASE WHEN i2.coin = 'Dime' THEN rolls * 5
      4          WHEN i2.coin = 'Quarter' THEN rolls * 10
      5          WHEN i2.coin = 'Nickel' THEN rolls * 2
      6          END dollars
      7          FROM inheritance i2
      8          WHERE i2.coin = i.coin);
     
    51334 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT coin, roll_val, COUNT(*)
      2  FROM inheritance
      3  GROUP BY coin, roll_val;
     
    COIN           ROLL_VAL   COUNT(*)
    ------------ ---------- ----------
    Dime                  5       4667
    Quarter              10      11667
    Nickel                2      35000

    SQL>
    Notice that as we changed the number of non-aggregate columns (columns not like sum(), avg(), count()) we changed the group by expression. Had we not done this we would have seen an ORA-00979 error, "Not a group by expression" which tells you that not all of the non-aggregate columns have been included. You can have more than one aggregate column in your list, too:

    Code (SQL):
    SQL> SELECT coin, roll_val, COUNT(*), SUM(rolls), avg(rolls)
      2  FROM inheritance
      3  GROUP BY coin, roll_val;
     
    COIN           ROLL_VAL   COUNT(*) SUM(ROLLS) AVG(ROLLS)
    ------------ ---------- ---------- ---------- ----------
    Dime                  5       4667       4667          1
    Nickel                2      35000      35000          1
    Quarter              10      11667      11667          1

    SQL>
    and as long as you don't increase the number of non-aggregate columns your group by clause will be fine (as shown above).