[Oracle / 오라클] group by, count, sum

tb_department 테이블 조회 

select * from tb_department;

 

tb_student 테이블 조회

select * from tb_student;

SELECT
    d.department_name,
    a.*
FROM
    (
        SELECT
            department_no,
            COUNT(student_no) count
        FROM
            tb_student
        GROUP BY
            department_no
    )              a,
    tb_department  d
WHERE
    a.department_no = d.department_no
ORDER BY
    count DESC;

 

 

 

 

select * from departments;

select * from employees

 

select 하고자 하는 department_id, department_name 은 group_by 로 묶어준다. 

 

SELECT
    d.department_id,
    d.department_name,
    SUM(e.salary) salary
FROM
    employees    e,
    departments  d
WHERE
    e.department_id = d.department_id
GROUP BY
    d.department_id,
    d.department_name;