學習筆記專區

--查出以部門編號分組，各部門薪水總和

select DEPARTMENT_ID,sum(SALARY)--3.找出需要分組的欄目、需要加總的欄目

from EMPLOYEES--1.找出所需要的資料表

group by DEPARTMENT_ID--2.找出要分組的項目名稱

--查出以部門名稱分組，薪水總和

select d.DEPARTMENT_NAME,sum(SALARY)sum1--4.找出需要分組的欄目、需要加總的欄目，sum1為給予的欄位名稱

from EMPLOYEES e join DEPARTMENTS d--1.找出所需要的表格(所需欄目分在不同表格中)

on e.DEPARTMENT_ID = d.DEPARTMENT_ID--2.篩選欄目告知一致

group by d.DEPARTMENT_name--3.找出要分組的項目名稱

--查出以部門編號、名稱分組的薪水總和

select d.DEPARTMENT_ID,d.DEPARTMENT_NAME,sum(SALARY)sum1

from EMPLOYEES e right join DEPARTMENTS d

on e.DEPARTMENT_ID = d.DEPARTMENT_ID

group by d.DEPARTMENT_name,d.DEPARTMENT_ID--先依照DEPARTMENT_name分類，再依照DEPARTMENT_ID分類，但是因為他們一樣所以看起來只有一個分類

order by DEPARTMENT_ID--已經說過兩邊ID一樣，所以不用在前面加上資料表名稱(別名)

--group by 展示

select DEPARTMENT_ID,job_id,sum(salary)

from EMPLOYEES

group by DEPARTMENT_ID,job_id

先依DEPARTMENT_ID分組，再依job_id分組最後顯示job_id的sum(salary)