# GROUP BY...WITH ROLL UP 分组统计后的再合计

GROUP BY...WITH ROLL UP 是group by 的高配版，在group by 分组之后，再合计总数。

ex：求8月1号到9号每日投资人数

SELECT
COUNT(DISTINCT investor_uid)AA,
FROM
lzh_borrow_investor
WHERE
AND '2016-8-10 00:00:00'
GROUP BY

AA           BB

3 2016-08-01
1 2016-08-02
3 2016-08-03
4 2016-08-04
1 2016-08-05
1 2016-08-06
3 2016-08-07
6 2016-08-08
4 2016-08-09
25

SELECT
CASE WHEN (GROUPING(SEX) = 1) THEN '统计-ROLLUP'
ELSE ISNULL(SEX, 'UNKNOWN')
END AS SEX ,
CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN '统计-ROLLUP'
ELSE ISNULL([DEPARTMENT], 'UNKNOWN')
END AS [DEPARTMENT],
COUNT(0)
FROM DBO.[STAFF]
GROUP BY   SEX,[DEPARTMENT]   WITH ROLLUP

with cube 与with rollup的不同点在于：

SELECT
CASE WHEN (GROUPING(SEX) = 1) THEN '统计-CUBE'
ELSE ISNULL(SEX, 'UNKNOWN')
END AS SEX ,
CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN  '统计-CUBE'
ELSE ISNULL([DEPARTMENT], 'UNKNOWN')
END AS [DEPARTMENT],
COUNT(0)
FROM DBO.[STAFF]
GROUP BY   SEX,[DEPARTMENT]  WITH CUBE

10-06 1328

05-06 1.6万

03-30 291

12-12 1100

03-16 3.2万

09-19 181

08-31 1991

11-07 4260

06-07 1349

12-02 1735