How to count distinct value with rollup?

Question

I've a table which contain log about network incidents. Each incident is attached to a site and an department.

I use a aggregation query to summarize all my incidents (total downtime time, ...) and I use the ROLLUP function:

SELECT i.annee, i.mois, i.siteId, count(i.id), sum(i.downtime)
FROM incident i
GROUP BY i.annee, i.mois, i.siteId, i.id
WITH ROLLUP

I group my incidents by site, but I would like add a column which count for every line the number of different department concerned by these incidents.

So I should add a filed: COUNT(DISTINCT i.department), but I heard that it is incompatible with ROLLUP function

Do you know a workaround to to it?

Answer 1

Here are 3 different options. I don't have SQL 2000 to test against, but this should work. Each option may perform differently given the table size and data structure.

NOTE: for anyone seeing this answer, if you are using SQL Server 2008 R2 or above, then COUNT DISTINCT works even when using ROLLUP. It may work with SQL 2005, but I don't have an instance of SQL 05 to test against.

Using a subquery:

SELECT t.annee, t.mois, t.siteId, t.id, t.cnt, t.downtime
    , (
        SELECT count(distinct i2.department) 
        FROM incident i2 
        WHERE (i2.annee = t.annee 
                    or i2.annee is null and t.annee is null and t.isgroup_annee = 0 
                    or t.isgroup_annee = 1)
            and (i2.mois = t.mois 
                    or i2.mois is null and t.mois is null and t.isgroup_mois = 0 
                    or t.isgroup_mois = 1)
            and (i2.siteId = t.siteId 
                    or i2.siteId is null and t.siteId is null and t.isgroup_siteId = 0  
                    or t.isgroup_siteId = 1)
            and (i2.id = t.id 
                    OR i2.id is null and t.id is null and t.isgroup_id = 0 
                    OR t.isgroup_id = 1)
        ) as departmentCnt
FROM (
    SELECT i.annee, i.mois, i.siteId, i.id, count(i.id) as cnt, sum(i.downtime) as downtime 
        , grouping(i.annee) AS isgroup_annee
        , grouping(i.mois) AS isgroup_mois
        , grouping(i.siteId) AS isgroup_siteId
        , grouping(i.id) AS isgroup_Id
    FROM incident i
    GROUP BY i.annee, i.mois, i.siteId, i.id
    WITH ROLLUP
) t
;

Using a join:

SELECT t.annee, t.mois, t.siteId, t.cnt, t.downtime
    , count(distinct i2.department) as departmentCnt
FROM (
    SELECT i.annee, i.mois, i.siteId, i.id, count(i.id) as cnt, sum(i.downtime) as downtime 
        , grouping(i.annee) AS isgroup_annee
        , grouping(i.mois) AS isgroup_mois
        , grouping(i.siteId) AS isgroup_siteId
        , grouping(i.id) AS isgroup_Id
    FROM incident i
    GROUP BY i.annee, i.mois, i.siteId, i.id
    WITH ROLLUP
) t
LEFT JOIN incident i2 
    ON (i2.annee = t.annee 
            or i2.annee is null and t.annee is null and t.isgroup_annee = 0 
            or t.isgroup_annee = 1)
    and (i2.mois = t.mois 
            or i2.mois is null and t.mois is null and t.isgroup_mois = 0 
            or t.isgroup_mois = 1)
    and (i2.siteId = t.siteId 
            or i2.siteId is null and t.siteId is null and t.isgroup_siteId = 0  
            or t.isgroup_siteId = 1)
    and (i2.id = t.id 
            OR i2.id is null and t.id is null and t.isgroup_id = 0 
            OR t.isgroup_id = 1)
GROUP BY t.annee, t.mois, t.siteId, t.id, t.cnt, t.downtime, t.isgroup_annee, t.isgroup_mois, t.isgroup_siteId, t.isgroup_Id
;

using UNIONs to perform the ROLLUP manually:

SELECT i.annee, i.mois, i.siteId, i.id, count(i.id) as cnt, sum(i.downtime) as downtime, count(distinct i.department) as departmentCnt
FROM incident i
GROUP BY i.annee, i.mois, i.siteId, i.id
UNION ALL
SELECT i.annee, i.mois, i.siteId, null as id, count(i.id) as cnt, sum(i.downtime) as downtime, count(distinct i.department) as departmentCnt
FROM incident i
GROUP BY i.annee, i.mois, i.siteId
UNION ALL
SELECT i.annee, i.mois, null as siteId, null as id, count(i.id) as cnt, sum(i.downtime) as downtime, count(distinct i.department) as departmentCnt
FROM incident i
GROUP BY i.annee, i.mois
UNION ALL
SELECT i.annee, null as mois, null as siteId, null as id, count(i.id) as cnt, sum(i.downtime) as downtime, count(distinct i.department) as departmentCnt
FROM incident i
GROUP BY i.annee
UNION ALL
SELECT null as annee, null as mois, null as siteId, null as id, count(i.id) as cnt, sum(i.downtime) as downtime, count(distinct i.department) as departmentCnt
FROM incident i
;