기초문법 [ GROUPING , with rollup , is not null ]

 

--WHERE 타겟 is not null
null인값은 표시하지 않음.

--group by
같은 데이터를 한데 묶음.

group by Title
Title이 같은 값을 한곳에 모음.

--having
묶인 그룹내에 검색추가.

SELECT 
  Title
,  COUNT(Title) as cnt
FROM Person.Contact
group by Title
HAVING Title = 'Ms.'
or Title = 'Mr.'

--with rollup
전체리스트의 합계라인추가.

SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;

 

--GROUPING 그룹처리될때 원본인지 모여있는 값인지 표시.
SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
,  GROUPING(CountryRegionName) as c1
,  GROUPING(StateProvinceName) as c2
,  GROUPING(City)    as c3
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;
위와 같을때는
c1에는 '0'(원본) 또는 '1'(모인값)로 표시됨.

 

when(GROUPING(CountryRegionName) = 1)then '총계'
when(GROUPING(StateProvinceName) = 1)then '국별합계'