Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: learning on schema hr

    hi,
    i have two tables
    employees with department_id, salary...
    departments with department_id, department_name...

    and i want to get result as with
    SELECT department_id, MIN(salary), MAX(salary), ROUND(AVG(salary),2), SUM(salary), COUNT(*)
    FROM employees
    GROUP BY department_id;


    but with column department_name

    i tried this, but it has error inside
    SELECT department_id, department_name, MIN(salary), MAX(salary), ROUND(AVG(salary),2), SUM(salary), COUNT(*)
    FROM employees, departments
    WHERE employees.department_id=deparments.department_id
    GROUP BY department_id;


    any idea how to fix?
    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT department_id,
           department_name,
           MIN(salary),
           MAX(salary),
           Round(AVG(salary), 2),
           SUM(salary),
           COUNT(*)
    FROM   employees,
           departments
    WHERE  employees.department_id = deparments.department_id
    GROUP  BY department_id,
              department_name;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/oracle/16742...ml#post6535322
    are you two taking the same class?

    I suggest working together to prevent double postings.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2012
    Posts
    6
    thx, but it still dont work (Error code 918, SQL state 42000: ORA-00918: column ambiguously defined)
    dont you have mistake inside?

    and no... we dont take same class, i dont know him and i dont understand what is common in our problems..

  5. #5
    Join Date
    Feb 2005
    Posts
    57
    ORA-00918 column ambiguously defined

    Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

    Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

  6. #6
    Join Date
    Jan 2012
    Posts
    6
    ok, i tried apply fix by some example on web, but still same error

    SELECT department_id, d.department_name, MIN(salary), MAX(salary), ROUND(AVG(salary),2), SUM(salary), COUNT(salary)
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    GROUP BY department_id, department_name;

    pls, correct me if you know where i have mistake

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Precede ALL column names with table aliases. Always. Even if you write a query on a single table. Take it as a best practice.

  8. #8
    Join Date
    Jan 2012
    Posts
    6
    oh thx, that is advice i was looking for

    so this is right:

    SELECT e.department_id, d.department_name, MIN(e.salary), MAX(e.salary), ROUND(AVG(e.salary),2), SUM(e.salary), COUNT(e.salary)
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    GROUP BY e.department_id, d.department_name;

  9. #9
    Join Date
    Feb 2012
    Posts
    1
    its working!!!!

Posting Permissions

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