CODEWITHSUNDEEP
mysqlsql
Shaharyar
Shaharyar

Reputation: 12439

Add summary row

I am generating data-set by UNION three data-sets. Then I want to add a summary row at the end of the data-set for just one column.

Data:

id  uid type    seen
1   20  A       1
2   20  B       1
3   20  C       1
4   6   A       1
5   1   A       1
6   3   A       0
7   6   A       0
8   1   B       0

Query:

SELECT * FROM
(
    (SELECT
            type,
            GROUP_CONCAT(DISTINCT uid) AS users,
            COUNT(type) AS typeCount,
            MIN(seen) as seen
    FROM `test2`
    WHERE type = 'A'
    GROUP BY type)

    UNION

    (SELECT
            type,
            GROUP_CONCAT(DISTINCT uid) AS users,
            COUNT(type) AS typeCount,
            MIN(seen) as seen
    FROM `test2`
    WHERE type = 'B'
    GROUP BY type)

    UNION

    (SELECT
            type,
            GROUP_CONCAT(DISTINCT uid) AS users,
            COUNT(type) AS typeCount,
            MIN(seen) as seen
    FROM `test2`
    WHERE type = 'C'
    GROUP BY type)
) as result

Query Results:

id  uid         type    seen
A   20,6,1,3    5       0
B   20,1        2       0
C   20          1       1

Expected Results:

id      uid         type    seen
A       20,6,1,3    5       0
B       20,1        2       0
C       20          1       1
total                       2      -- counting `0` here

PS: I know the query can be simplified by just using GROUP BY but it is not the real scenario, it is just a minimal explanation of the problem.

Upvotes: 0

Views: 115

Answers (1)

Susilo
Susilo

Reputation: 866

You can apply ROLLUP on your query..just leave your sub query as is right know then apply GROUP BY on them

SELECT type, MIN(users) users, MIN(typeCount) typeCount, MIN(seen) seen, IF(seen=0,1,0) zero_seen FROM
(

    --  previous sub query

) as result
GROUP BY type WITH ROLLUP

EDIT: ROLLUP only do sum or calculate grand total, to do count IF(seen=0,1,0) zero_seen added to count 0 seen..

Upvotes: 2

Related Questions