MySQL WITH ROLLUP – Handling NULL Values

group byMySQLnull

I tried to find this question somewhere else on here, and found a couple that were similar to it — but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.

Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:

Type    Person  Count
Type A  Person 1    10
Type A  Person 2    91
Type A  Person 3    13
Type A  Person 4    10
Type A  Person 5    2
Type A  NULL        126
Type B  Person 6    16
Type B  Person 7    63
Type B  Person 8    15
Type B  Person 9    22
Type B  Person 10   16
Type B  Person 11   3
Type B  Person 12   20
Type B  Person 13   44
Type B  NULL        198
NULL    NULL        360

Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"

And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."

Thanks!

Best Answer

Let's say your query looks like this:

SELECT type,person,COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

The NULL values coming out can be substituted with something like this...

SELECT
    IFNULL(type,'All Types') Type,
    IFNULL(person,'All Persons') Person
    COUNT(*) `Count`
FROM mytable GROUP BY type,person WITH ROLLUP;

Using IFNULL() will forcibly put those Text String in place of the NULL for each level of the GROUP BY. As an example, here are past posts where I actively substitute NULL in my answer involving WITH ROLLUP:

Sep 13, 2011 : Determining max database and table size supported and present size
Dec 01, 2011 : MySQL Workbench Database Sizes
Jan 11, 2013 : Translating backup size to database size

From these posts I have this example,

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score, IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",
B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 3 pw) A) AA
ORDER BY schemaname,schema_score,engine_score;

Here is the output from one of my client's DB Servers:

+------------------------------------+----------------------+----------------------+----------------------+
| Statistic                          | Data Size            | Index Size           | Table Size           |
+------------------------------------+----------------------+----------------------+----------------------+
| MyISAM Tables for dev_oxygen       |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for dev_oxygen       |             1.480 GB |             0.506 GB |             1.986 GB |
| Storage for dev_oxygen             |             1.480 GB |             0.506 GB |             1.986 GB |
| MyISAM Tables for dumbpdb          |             0.001 GB |             0.001 GB |             0.002 GB |
| InnoDB Tables for dumbpdb          |             1.552 GB |             0.583 GB |             2.135 GB |
| Storage for dumbpdb                |             1.553 GB |             0.584 GB |             2.137 GB |
| InnoDB Tables for duplicate_oxygen |             0.166 GB |             0.073 GB |             0.239 GB |
| Storage for duplicate_oxygen       |             0.166 GB |             0.073 GB |             0.239 GB |
| InnoDB Tables for facebook_app     |             0.000 GB |             0.000 GB |             0.000 GB |
| Storage for facebook_app           |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB Tables for generationo      |             0.016 GB |             0.002 GB |             0.018 GB |
| Storage for generationo            |             0.016 GB |             0.002 GB |             0.018 GB |
| InnoDB Tables for mantisbt         |             0.000 GB |             0.000 GB |             0.001 GB |
| Storage for mantisbt               |             0.000 GB |             0.000 GB |             0.001 GB |
| MyISAM Tables for oxygen           |             0.003 GB |             0.002 GB |             0.005 GB |
| InnoDB Tables for oxygen           |             1.771 GB |             0.739 GB |             2.510 GB |
| Storage for oxygen                 |             1.774 GB |             0.741 GB |             2.515 GB |
| Storage for All Databases          |             4.990 GB |             1.907 GB |             6.897 GB |
+------------------------------------+----------------------+----------------------+----------------------+
18 rows in set (12.67 sec)

Notice I check the ISNULL() of each column and compare IF() function calls to produce a single column output. In your particular case, you are going to need one of these two queries

QUERY #1

SELECT
    IF(ISNULL(type)=1,'Grand Total',
        IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT type,person,COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

QUERY #2

SELECT
    IF(type='AllDone','Grand Total',
        IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
        CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ) Statistic,`Count`
FROM
(
    SELECT
        IFNULL(type,'AllDone') type,
        IFNULL(person,'AllDone') person,
        COUNT(*) `Count`
    FROM mytable GROUP BY type,person WITH ROLLUP
) A;

Here is some sample data:

DROP DATABASE IF EXISTS ryanodonnell;
CREATE DATABASE ryanodonnell;
USE ryanodonnell
CREATE TABLE mytable
(
    id int not null auto_increment,
    type varchar(20) not null,
    person varchar(20) not null,
    key type_person_index (type,person),
    primary key (id)
);
INSERT INTO mytable (type,person) VALUES
('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');

Here is that sample data loaded:

mysql> DROP DATABASE IF EXISTS ryanodonnell;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE ryanodonnell;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanodonnell
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     type varchar(20) not null,
    ->     person varchar(20) not null,
    ->     key type_person_index (type,person),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytable (type,person) VALUES
    -> ('Type A','Person 1'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 5'),('Type A','Person 2'),('Type A','Person 3'),
    -> ('Type A','Person 2'),('Type A','Person 3'),('Type A','Person 1'),
    -> ('Type A','Person 5'),('Type A','Person 1'),('Type A','Person 5'),
    -> ('Type A','Person 3'),('Type A','Person 4'),('Type A','Person 5'),
    -> ('Type B','Person 1'),('Type B','Person 2'),('Type B','Person 3'),
    -> ('Type B','Person 2'),('Type B','Person 3'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 4'),('Type B','Person 5'),
    -> ('Type B','Person 4'),('Type B','Person 5'),('Type B','Person 4'),
    -> ('Type B','Person 3'),('Type B','Person 1'),('Type B','Person 3');
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql>

Now, here is the output of Query #1:

mysql> SELECT
    ->     IF(ISNULL(type)=1,'Grand Total',
    ->         IF(ISNULL(person)=1,CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT type,person,COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set (0.27 sec)

mysql>

Now, here is the output of Query #2:

mysql> SELECT
    ->     IF(type='AllDone','Grand Total',
    ->         IF(person='AllDone',CONCAT(QUOTE(type),' Subtotal'),
    ->         CONCAT(QUOTE(type),' Subtotal for ',QUOTE(person)))
    ->     ) Statistic,`Count`
    -> FROM
    -> (
    ->     SELECT
    ->         IFNULL(type,'AllDone') type,
    ->         IFNULL(person,'AllDone') person,
    ->         COUNT(*) `Count`
    ->     FROM mytable GROUP BY type,person WITH ROLLUP
    -> ) A;
+----------------------------------+-------+
| Statistic                        | Count |
+----------------------------------+-------+
| 'Type A' Subtotal for 'Person 1' |     3 |
| 'Type A' Subtotal for 'Person 2' |     3 |
| 'Type A' Subtotal for 'Person 3' |     4 |
| 'Type A' Subtotal for 'Person 4' |     1 |
| 'Type A' Subtotal for 'Person 5' |     4 |
| 'Type A' Subtotal                |    15 |
| 'Type B' Subtotal for 'Person 1' |     2 |
| 'Type B' Subtotal for 'Person 2' |     2 |
| 'Type B' Subtotal for 'Person 3' |     5 |
| 'Type B' Subtotal for 'Person 4' |     4 |
| 'Type B' Subtotal for 'Person 5' |     2 |
| 'Type B' Subtotal                |    15 |
| Grand Total                      |    30 |
+----------------------------------+-------+
13 rows in set, 2 warnings (0.00 sec)

mysql>

Give it a Try !!!

Best Answer

QUERY #1

QUERY #2

Related Solutions

MySQL Default – NULL or NOT NULL?

Mysql – Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

Related Question