SQL - learn grouping

sql.sql

;with persons as (
	select id, name, gender, color
	from (
		values
			 (1,'kevin','m','blue')
			,(2,'owen','m','blue')
			,(3,'wendy','f','pink')
			,(4,'emrys','m','black')
			,(5,'aria','f','brown')
	) q (id, name, gender, color)
)

select	
		x = 'x'

		,gender
		,color

		,[G-gender] = grouping(gender)
		,[G-color] = grouping(color)
		,[G-all] = grouping_id(gender, color)	--this is because the bitmask value is binary '11' which equals 2+1 = 3

		,counts = count(*)

from	persons
group by --gender, color with rollup
	grouping sets (
		()	--think of this as the straight up aggregate
		,gender
		,color
		,(gender, color)		
	)

order by [G-all]

/*
x    gender color G-gender G-color G-all       counts
---- ------ ----- -------- ------- ----------- -----------
x    m      black 0        0       0           1
x    m      blue  0        0       0           2
x    f      brown 0        0       0           1
x    f      pink  0        0       0           1
x    f      NULL  0        1       1           2
x    m      NULL  0        1       1           3
x    NULL   pink  1        0       2           1
x    NULL   brown 1        0       2           1
x    NULL   blue  1        0       2           2
x    NULL   black 1        0       2           1
x    NULL   NULL  1        1       3           5


*/