■ select 구문의실행순서

 select  컬럼명  -- 5
 from    테이블명-- 1
 where   조건절  -- 2
 group by    절  -- 3
 having      절  -- 4
 order by    절  -- 6

■ 집계함수 count(), sum(), avg(), min(), max()

● count()

/*
count() 함수- 수를세아리다
: 현재select 에의해서반환되고있는레코드(컬럼)의갯수를반환
문법: count(컬럼명)
*/

-- 서울에사는회원은몇명??
select COUNT(*) from userTbl where addr = '서울'; 

-- 아시아와유럽에속한나라의갯수?? 7개
select COUNT(*) from tblCountry where cont in ('AS','EU'); -- or 조건

-- count() 함수의인자값이(*) 이거나(컬럼명) 일떄의차이점
select COUNT(*) from tblgroup; -- 직원수25명
select COUNT(name) from tblGroup; -- 직원의이름이총25개인상황
select COUNT(achieve) from tblGroup; -- 인자값으로컬럼명을줬을때!!!
-- null값은카운트에서제외(***)

-- 실적보너스1,000,000 원-> 1/n 분배
select 10000000 / 25; -- (X)
select 10000000 / COUNT(*) from tblGroup; -- (O) 모든직원에게분배
select 10000000 / COUNT(achieve) from tblGroup; -- 실적null 인사람뺴고분배

-- 실적이있는사원수?
select COUNT(achieve) from tblGroup; 

-- 실적이없는사원수?
select COUNT(*) - COUNT(achieve) from tblGroup; 
select COUNT(*) from tblGroup where achieve is null 

-- 근무개월수가1년이상2년이하인직원수?
select COUNT(workmonth) from tblGroup where workMonth between 12 and 24

--중복값제거
select distinct(cont) from tblCountry; 
select count(distinct(cont)) from tblcountry;

● sum()

/*
sum()함수
문법: sum(컬럼명)
컬럼값의총합을반환
*/

select achieve from tblGroup; -- 개인별실적
select SUM(achieve) from tblGroup; -- 총합실적

--Error
select SUM(name) from tblGroup; -- 컬럼(name)이 숫자가 아닐경우는 에러가 난다.

● avg()

/*
avg() 함수
문법: avg(컬럼명)
: 컬럼값의평균을반환하는함수
: null 레코드는제외!!!! (count() 와같다.)
*/

select AVG(achieve) from tblGroup; -- 개인당평균
select avg(workmonth) from tblGroup;
select sum(achieve) as [총실적수], avg(achieve) as [평균실적수] from tblGroup;

● min(), max()

/*
max(컬럼명) 함수: 최대값
- 컬럼명중최대값하나를반환시킨다.
min(컬럼명) 함수: 최소값
- 컬럼명중최소값하나를반환시킨다.
문자형역시산출가능
*/

-- 월급이가장많은액수?
select COUNT(salary) from tblGroup;
select SUM(salary) from tblGroup;
select MAX(salary) from tblGroup;
select MIN(salary) from tblGroup;

-- 에러! 집계함수는where절에못쓴다.
select name from tblGroup where salary = MAX(salary);

select MAX(name) from tblGroup; -- 가능
select MIN(name) from tblGroup; -- 가능

■ group by

/*
- group by (그룹을묶어서무언가할수있는일을실행하겠다?)
- 특정컬럼값이동일한레코드의통계값반환
- 집계함수와같이사용함(***) -> 직계함수의결과를그룹지어서보자
- group by를이용해서select 실행시결과리스트안에는...
1. 집계함수
2. group by의대상컬럼이올수있다.
*/

-- 전직원의평균근무개월수? -> AVG()
select AVG(workmonth) from tblGroup; 

-- 부서별직원의평균근무개월? -> AVG() + 조건
select distinct(depart) from tblGroup; -- 관리부, 생산부, 영업부, 지원부
select AVG(workmonth) from tblGroup where depart = '관리부'; 
select AVG(workmonth) from tblGroup where depart = '생산부'; 
select AVG(workmonth) from tblGroup where depart = '영업부'; 
select AVG(workmonth) from tblGroup where depart = '지원부'; 

-- depart 컬럼값이동일한값을갖는레코드끼리묶자!!(그룹화)
select AVG(workmonth) as [근무개월], depart from tblGroup group by depart;

-- 에러구문,
select workmonth from tblGroup group by depart; 
-- group by depart로 부서를 묶었는데 거기서 workmonth를 가져오라고 하니
-- 근무개월수가 부서별 개인마다 다 다른데 어떤걸불러와야 할지모른다.
-- 그러므로 집계함수나 대상컬럼외에는올수없다.

-- 각부서별직원수?
select depart, COUNT(*)as[부서별직원] from tblGroup group by depart;

-- 각부서별최대월급은?
select depart, MAX(salary) as [부서별월급] from tblGroup group by depart;

-- 대륙별국가수?
select cont, COUNT(*) from tblCountry group by cont;

-- 사용자아이디별로물품에상관없이총구매갯수?
select userid, sum(amount) from buyTbl group by userid;

-- 사용자아이디별로물품에상관없이총구매액수?
select userid, SUM(price * amount) from buyTbl group by userid;

-- 분야별(type) 도서의평균가격?, 도서몇권?, 가장비싼책가격?, 가장싼책가격?
select [TYPE], AVG(price) as [평균가격], COUNT(type)as [도서몇권], MAX(price)as [비싼책], MIN(price) as [싼책] from titles group by [type];

■ having

/*
- having
: group by 의조건절역할
*/

--각부서별평균월급?
select depart, AVG(salary) from tblgroup group by depart;

-- 각부서별평균월급이180만원이상인부서를나타내시오
select depart, AVG(salary) from tblgroup group by depart having AVG(salary) > 180;

-- 각부서별평균월급이180만원이상인부서를나타내고, 평균월급정렬
select depart, AVG(salary) from tblgroup group by depart having AVG(salary) > 180
order by AVG(salary) desc;

--(가장우선하는전제조건) 12개월근무가넘은사람들에한해서..
-- 평균월급180만원이상인부서별평균월급? 평균월급정렬
select depart, avg(salary) as [월급] from tblgroup where workmonth >= 12 group by depart
having avg(salary) > 180 order by avg(salary) desc;

-- 위의select와차이점이해!!
select depart, avg(salary) as [월급] from tblgroup group by depart having avg(salary) >180
and AVG(workmonth) >= 12 order by avg(salary) desc;

-- 관리부, 생산부대상-> 부서별평균월급
select depart, AVG(salary) from tblGroup group by depart
having depart = '관리부' or depart = '생산부'; -- 비추천

select depart, AVG(salary) from tblGroup group by depart
having depart in('관리부','생산부'); -- 추천구문(0)

■ compute

/*
- compute ( 사용도떨어짐^.* )
: select 실행후별도의집계결과를반환
: 집계함수과같이사용
: select의대상은집계함수
*/

select * from tblGroup;
select AVG(salary) from tblGroup; -- 전체직원의평균월급

select * from tblGroup compute avg(salary); -- 위의두문장을한문장으로표현

-- 일이끝나고난뒤별도의집계합수값을실행()
select * from tblGroup compute sum(salary); -- tblgroup을다가져온뒤월급총합을보여준다.

■ Datetime 함수

/*
- 날짜시간함수
: Datetime 형
*/

select GETDATE() as [현재시간];

create table tblMemo
(
    seq int identity(1,1) primary key, -- 메모번호
    memo nvarchar(1000) not null, -- 메모내용
    writedate datetime not null -- 작성시간
)

insert into tblMemo (memo,writedate)
values('메모입니다.~', GETDATE()); -- getdate() 직접입력

select * from tblMemo

create table tblMemo2
(
    seq int identity(1,1) primary key, -- 메모번호
    memo nvarchar(1000) not null, -- 메모내용
    writedate datetime default(getdate()) not null -- 작성시간
)

insert into tblMemo2(memo) values('메모입력했다잉~'); -- gatdate() 기본값으로자동입력

select * from tblMemo2;

insert into tblMemo2(memo) values('메모다잉~'); -- 시간을 두고 F5 입력 ↓
insert into tblMemo2(memo) values('메모다잉~1');
insert into tblMemo2(memo) values('메모다잉~2');
insert into tblMemo2(memo) values('메모다잉~3');
insert into tblMemo2(memo) values('메모다잉~4');
insert into tblMemo2(memo) values('메모다잉~5');

select writedate from tblmemo2;

-- 가장최근에쓴메모가언제쓴메모??
select top 1 writedate from tblMemo2 order by writedate desc;

select MAX(writedate) from tblmemo2; -- 위아래같은구문

■ 추출 DatePart

/*
- 추출(년,월,일)
DatePart(year,month,day)
*/

select DATEPART(YEAR, max(writedate)) from tblMemo2;
select DATEPART(MONTH, max(writedate)) from tblMemo2;
select DATEPART(DAY, max(writedate)) from tblMemo2;
select DATEPART(HOUR, max(writedate)) from tblMemo2;
select DATEPART(MINUTE, max(writedate)) from tblMemo2;
select DATEPART(SECOND, max(writedate)) from tblMemo2;
select DATEPART(YEAR, DAY(writedate)), writedate from tblMemo2;

-- 6월에출간된도서명?
select * from titles;
select * from titles where DATEPART(MONTH, pubdate) = 6;
select DATEPART(MONTH,pubdate) from titles; -- 출간일의그달만표시

-- 2000년이후출간도서? 2건
select * from titles where pubdate > '2000-01-01';

■ datetime 연산

/*
- datetime 연산
DATEADD(형식,시간,시각) return 시각: 시각+ 시간= 시각
DATEDIFF(형식,시간,시각) return 시간: 시각- 시각= 시간
*/

select * from tblMemo2;

-- 메모시간+ 100일
select writedate, DATEADD(DAY,100,writedate) from tblMemo2; -- DAY + 100일= writedate에적용

-- 메모시간- 100일
select writedate, DATEADD(DAY,-100,Writedate) from tblMemo2;

-- 메모시간+ 100년
select writedate, DATEADD(YEAR,100,Writedate) from tblMemo2;

-- 메모를각각적은후얼마나지났는지
-- 원하는건: 현재시각- 기록시각= 시간
select writedate from tblMemo2;
select writedate, DATEDIFF(HOUR, writedate, GETDATE()) from tblMemo2;

-- DATEDIFF(HOUR,기록시간,현재시간()) : 기록시간- 현재시간을시간으로나타낸다.
select writedate from tblMemo2;
select LEFT(writedate,4) from tblMemo2;

MS-SQL count avg group by compute having min DateTime max sum

김키티