제목 : 활용예제 : 구분별 부서 통계
|
글번호:
|
|
62
|
|
작성자:
|
|
레드플러스
|
|
작성일:
|
|
2009/02/04 오후 2:46:00
|
|
조회수:
|
|
8700
|
|
파일:
|
|
(다운로드 권한이 없습니다.)
|
Use tempdb
Go
-- create table
-- Drop Table Cars
Create Table Cars
(
Num Int Identity(1, 1) Primary Key,
Dept VarChar(10), -- 부서
Div Int, -- 구분(1:수리신청,2:예방검사,3:정기검사)
Pay Int -- 금액
)
Go
-- insert sample data
Insert Cars Values('총무', 1, 500)
Go
Insert Cars Values('총무', 1, 100)
Go
Insert Cars Values('총무', 2, 300)
Go
Insert Cars Values('총무', 3, 200)
Go
Insert Cars Values('생산', 1, 700)
Go
Insert Cars Values('생산', 3, 400)
Go
Insert Cars Values('생산', 3, 150)
Go
Insert Cars Values('판매', 1, 400)
Go
Insert Cars Values('판매', 2, 600)
Go
Insert Cars Values('판매', 2, 50)
Go
-- respective output data
--부서 수리 금액 정기검사 금액 예방검사 금액
------------ ----------- ----------- ----------- ----------- ----------- -----------
--생산 1 700 2 550 0 NULL
--총무 2 600 1 200 1 300
--판매 1 400 0 NULL 2 650
Select * From Cars
Go
Select Dept, COUNT(Div) As 개수, SUM(Pay) As 금액
From Cars
Group By Dept
Go
Select C.Dept As '부서',
(Select Count(*) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 1) AS '수리',
(Select SUM(Pay) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 1) AS '금액',
(Select Count(*) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 3) AS '정기검사',
(Select SUM(Pay) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 3) AS '금액',
(Select Count(*) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 2) AS '예방검사',
(Select SUM(Pay) From Cars C1 Where C1.Dept = C.Dept And C1.Div = 2) AS '금액',
(Select SUM(Pay) From Cars C1 Where C1.Dept = C.Dept) As '전체합계'
From Cars C
Group By C.Dept
Order By C.Dept Asc
Go