Results 1 to 11 of 11

Thread: group by

  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Exclamation Unanswered: group by

    hai,
    if i have three columns deptno,ename,sal and i would like to group the results with the deptno and the avg sal of each deptno how can i do that? if i use group by deptno it is giving error not a group by function for ename and sal.how can i solve that?when can we use group by without giving error?
    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Average salary per deptno is calculated like this:

    SELECT deptno, AVG(sal)
    FROM emp
    GROUP BY deptno;

    Column "ename" doesn't matter in this case, because - if you want to include it into the query, it should look like this:

    SELECT deptno, ename, AVG(sal)
    FROM emp
    GROUP BY deptno, ename;

    But now you wouldn't calculate average salary per department, but per department AND employee, which is, actually (in Scoot's schema), average per employee.

    This is SELECT statement's info. Search for the GROUP BY clause and learn how to use it.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select t1.deptno, t1.ename, v1.avg_sal 
    from emp t1,
         (select deptno, avg(sal) 
          from emp
          group by deptno) v1
    where t1.deptno = v1.deptno
    -Chuck

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree, that is a way to include employee into a query. But, looking at the output it produces (modified a little bit; avg(sal) requires an alias)(an excerpt)
    Code:
        DEPTNO ENAME         AVG_SAL
    ---------- ---------- ----------
            10 CLARK      2916.66667
               KING       2916.66667
               MILLER     2916.66667
    it looks a little bit ... well, not right It says that in Clark, King and Miller work department 10; what is strange is average salary beside every one of them (as if it was THEIR salary).

    I'd rather do something like this:
    Code:
    SQL> break on deptno skip 2
    SQL> compute avg of sal on deptno
    SQL>
    SQL> select deptno, ename, sal from emp order by deptno;
    
        DEPTNO ENAME             SAL
    ---------- ---------- ----------
            10 CLARK            2450
               KING             5000
               MILLER           1300
    **********            ----------
    avg                   2916.66667
    This gives (to me, at least) output that is easier to understand.

  5. #5
    Join Date
    Jan 2006
    Posts
    11

    thanks

    thanks that helped a lot

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    use analytics and you can include any column you want.
    PHP Code:
    select deptnoenamesaltrunc(avg_per_deptavg_per_dept
    from 
    (
      
    select deptnoenamesal
         
    avg(salover (partition by deptno order by deptnoename avg_per_dept
      from emp 
    ); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wow

    This calculates "running" average salary per department.

    Why did you use inline view? Is it only because of the TRUNC function, or is there some other reason? Because, inline view's query itself gives the same result (only the average salary isn't truncated).

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    correct, you probably don't need the inline view.
    truncate works around the actual analytic line.
    Forget the order-by as well.

    Here is everything in one:
    PHP Code:
    select 
      deptno
    enamesal,  
      
    trunc(avg(salover (partition by deptno)) avg_sal_per_dept 
    from emp

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, I see ... so the whole analytic line should be truncated! Because applying TRUNC to AVG(sal) only (which I expected - and tried) ends with an error. Marvelous! Thank you for the explanation.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    you don't HAVE to truncate the avg, but (to me) it looks cleaner visually.
    You could ROUND it out or whatever you wanted.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, this part is understandable; I know that truncation was the artist's freedom of expression

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •