Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Unanswered: Calculation problem

    I read a course in SQL right now, and have a problem I can´t find a solution at.

    The first task is the following:
    View average salary for all customers in the table. I wrote the following SQL statement, which also worked:
    Code:
    SELECT ROUND(avg(nvl(årslön, 0))) AS Medellön FROM kund;
    But it is the second task that is the problem.
    The second task is the following:
    Show username, fname, enamn, arslan for those customers who have an annual income of less than average salary for all customers.
    It is working when I enter this SQL statement:
    Code:
    SELECT username, fnamn, enamn, nvl(årslön, 0) 
    FROM kund 
    GROUP BY årslön, username, fnamn, enamn 
    HAVING nvl(årslön, 0) < 355300;
    But I want to replace the 355 300 with a calculation. It is the same calculation that i used in the first task. I tried with this solution, but it does not work:
    Code:
    SELECT username, fnamn, enamn, nvl(årslön, 0) 
    FROM kund 
    GROUP BY årslön, username, fnamn, enamn 
    HAVING nvl(årslön, 0) < avg(nvl(årslön, 0));
    Does anyone have any suggestions for solution?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess code will explain it better than words.
    Code:
    SQL> select avg(sal) from emp;
    
      AVG(SAL)
    ----------
    2073.21429
    
    SQL> select ename, sal
      2  from emp
      3  group by ename, sal
      4  having sal < (select avg(sal)
      5                from emp
      6               );
    
    ENAME             SAL
    ---------- ----------
    ALLEN            1600
    MARTIN           1250
    MILLER           1300
    TURNER           1500
    ADAMS            1100
    JAMES             950
    SMITH             800
    WARD             1250
    
    8 rows selected.
    
    SQL>

  3. #3
    Join Date
    Feb 2011
    Posts
    9
    Thanks so much for you answer!

Posting Permissions

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