[그룹함수] employees 테이블에서 salary의 합이 가장 큰 department_id를 구하세요.

1.oracle

select department_id, sum(salary)
from employees
group by department_id
having sum(salary) = (select max(sum(salary))
                      from employees
                      group by department_id);

 

 

 

 

2. r

emp<-read.csv("c:/data/employees.csv",header=T,stringsAsFactors = F)

1)
emp1<-aggregate(SALARY~DEPARTMENT_ID, emp, sum)
result<-emp1[emp1$SALARY==max(emp1$SALARY),]

2)
library(plyr)
emp2<-ddply(emp,'DEPARTMENT_ID',summarise,sumsal=sum(SALARY))
result<-subset(emp2,sumsal==max(emp2$sumsal))

3)
library(dplyr)
emp3<-emp%>%
  group_by(DEPARTMENT_ID)%>%
  summarise(sumsal=sum(SALARY))
result<-filter(emp3,sumsal==max(emp3$sumsal))

4)
library(sqldf)
생략

5)
emp4<-tapply(emp$SALARY,emp$DEPARTMENT_ID,sum)
result<-emp4[emp4==max(emp4)]

 

 

 

3.python

import pandas as pd
emp=pd.read_csv("c:/data/employees.csv")

 

emp1 = emp['SALARY'].groupby(emp['DEPARTMENT_ID']).sum()
emp1.loc[emp1==emp1.max(),]