Tag Archives: with rollup

mysql count and group

Posted on by
Reply

In mysql you can count the total of rows, or you can count rows after grouping for a condition. Here we take a table and count every row, for each type of fruit:

mysql> select fruit,count(id) as cnt from inventory group by fruit;

+-------------+------------+
| fruit       | count(id)  |
+-------------+------------+
| apple       |       3496 |
| orange      |       3783 |
| mango       |         11 |
+-------------+------------+

Add the ‘with rollup’ statement, to get the total at the bottom:

mysql> select fruit,count(id) as cnt from inventory group by fruit;

+-------------+------------+
| fruit       | count(id)  |
+-------------+------------+
| apple       |       3496 |
| orange      |       3783 |
| mango       |         11 |
| NULL        |       7290 |
+-------------+------------+

You can add other conditional statements to refine what you need. Here we only want the fresh fruits that we have more than 3500 of:

mysql> select count(id) as cnt,fruit from inventory where status='fresh' group by fruit having cnt >3500;
+------+-----------+
| cnt  | fruit     |
+------+-----------+
| 3783 | apple     |
+------+-----------+

MySQL group by modulus

Posted on by
Reply

I have a series of scripts that acts on a subset of entries in a large (2M rows) MySQL table. There are 10 instances in the application, and each instance works on id‘s ending in 0-9 from a single table. The ending digit could also be considered the modulus. Instance 0 works on id‘s ending in 0, instance 1 works on id‘s ending in 1, etc. 

mysql> select id from table limit 10;
+---------+
| id      |
+---------+
| 1099138 | 
| 1811048 | 
|  794297 | 
|  985812 | 
|  424120 | 
|  353809 | 
|  478882 | 
|  506101 | 
| 2184449 | 
| 1189511 | 
+---------+

To see what progress the application is making or if it is stuck at I use a query grouping the id‘s remaining to be worked on by modulus:

mysql> select substr(id, -1) as modulus,count(*) from table where field='criteria' group by modulus with rollup;
+---------+----------+
| modulus | count(*) |
+---------+----------+
| 0       |        9 | 
| 1       |       14 | 
| 2       |       13 | 
| 3       |     1985 | 
| 4       |       10 | 
| 5       |       16 | 
| 6       |       16 | 
| 7       |       11 | 
| 8       |       10 | 
| 9       |       20 | 
| NULL    |     2104 | 
+---------+----------+

The substr(id,-1) takes the id and returns the last digit (-1) as modulus. Then it’s grouped by modulus, and using rollup is summed at the end. As you can see the application still has 2104 id‘s to work on, and something is different about instance 3. Either there is an uneven distribution of id‘s ending in 3 (probably not the case, as id is primary key auto increment), or instance 3 in the application is borked.