sql server - Skipping duplicated rolled up rows where data is NULL -
i create report organization hierarchy positions. hierarchy has 4 levels, position last leaf, path not 4 levels deep.
for example:
company > position
company > section > department > position
i want number of personnel.
declare @hierarchy table ( company nvarchar(50), section nvarchar(50), department nvarchar(50), unit nvarchar(50), position nvarchar(50), person nvarchar(50) ) insert @hierarchy values ('wd', 'weapons', 'it', 'officer', null, 'wile e.'), ('acme', 'weapons', 'it', 'network', 'engineer', 'brain'), ('acme', 'weapons', 'it', 'network', 'support', 'pinky'), ('acme', 'weapons', 'it', 'officer', null, 'bugs'), ('acme', 'weapons', 'it', 'officer', null, 'elmer'), ('acme', 'weapons', 'it', 'officer', null, 'daffy'), ('acme', 'weapons', 'tech', null, null, 'sylverster'), ('acme', 'anvils', 'officer', null, null, 'road') select company, section, department, unit, position, count(person) @hierarchy group rollup(company, section, department, unit, position) in above example, same 3 rows wd (wd, anvils, officer, null, null), 1 enough, because unit , position not applicable.
however, if put distinct in query, seemingly result
select distinct company, section, department, unit, position, count(person) @hierarchy group rollup(company, section, department, unit, position) what don't know if hack , got lucky, or if it's correct approach problem?
let's add grouping columns every level:
select company, section, department, unit, position, grouping(company) company, grouping(section) section, grouping(department) department, grouping(unit) unit, grouping(position) position, count(*) @hierarchy group rollup(company, section, department, unit, position) and @ duplicate values:
company section department unit position company section department unit position ---------- ---------- ---------- ---------- ---------- ------- ------- ---------- ---- -------- ----------- acme anvils officer null null 0 0 0 0 0 1 acme anvils officer null null 0 0 0 0 1 1 acme anvils officer null null 0 0 0 1 1 1 acme anvils null null null 0 0 1 1 1 1 acme weapons network engineer 0 0 0 0 0 1 acme weapons network support 0 0 0 0 0 1 acme weapons network null 0 0 0 0 1 2 acme weapons officer null 0 0 0 0 0 3 acme weapons officer null 0 0 0 0 1 3 acme weapons null null 0 0 0 1 1 5 acme weapons tech null null 0 0 0 0 0 1 acme weapons tech null null 0 0 0 0 1 1 acme weapons tech null null 0 0 0 1 1 1 acme weapons null null null 0 0 1 1 1 6 acme null null null null 0 1 1 1 1 7 wd weapons officer null 0 0 0 0 0 1 wd weapons officer null 0 0 0 0 1 1 wd weapons null null 0 0 0 1 1 1 wd weapons null null null 0 0 1 1 1 1 wd null null null null 0 1 1 1 1 1 null null null null null 1 1 1 1 1 8 (acme,anvils,officer) repeats 3 times, every time it's new level of grouping: department, department , unit, department, unit , position. it' because use columns in group by. values in unit , position null.
so, can add distinct query unique results columns - it's correct.
Comments
Post a Comment