Using ROLLUP MySQL

Databases
1

Hi,

I use ROLLUP MySQL for generates additional rows in the result set that represent subtotals and grand totals for the grouped data.

The grand total is calculated for all the data in the result set

My problem is in the last row of result set because on the last row of the second column is repetead the previous row, instead null value.

On the last row of first column I have instead correctly the Total value.

Can you help, please?

My code below

> mysql> SELECT
> 	COALESCE(AREA_Cod, 'Total') AREA_Cod, 
> 	Area_name,
> 	SUM( CASE WHEN Event_date = '2024-04-01' THEN ROUND( rest, 0 ) ELSE 0 END ) AS '2024-04-01',
> 	SUM( CASE WHEN Event_date = '2024-04-02' THEN ROUND( rest, 0 ) ELSE 0 END ) AS '2024-04-02',
> 	SUM( CASE WHEN Event_date = '2024-04-03' THEN ROUND( rest, 0 ) ELSE 0 END ) AS '2024-04-03'
> FROM
> 	`do_table` 
> WHERE
> 	Event_date BETWEEN '2024-04-01' AND '2024-04-03' 
> GROUP BY
> 	AREA_Cod WITH ROLLUP;
> 
> +----------+-----------------------------------+------------+------------+------------+
> | AREA_Cod | Area_name                         | 2024-04-01 | 2024-04-02 | 2024-04-03 |
> +----------+-----------------------------------+------------+------------+------------+
> | D7XX     | AREA AAA                          | 10         | 0          | 0          |
> | D8XX     | AREA BBB                          | 0          | 0          | 7          |
> | DEXX     | AREA CCC                          | 0          | 0          | 0          |
> | DJXX     | AREA DDD                          | 0          | 3          | 0          |
> | DKXX     | AREA EEE                          | 0          | 0          | 0          |
> | DLXX     | AREA FFF                          | 0          | 0          | 0          |
> | DNXX     | AREA GGG                          | 0          | 0          | 0          |
> | DUXX     | AREA HHH                          | 0          | 0          | 4          |
> | DVXX     | AREA LLL                          | 0          | 0          | 0          |
> | DWXX     | AREA MMM                          | 0          | 0          | 0          |
> | DXXX     | AREA NNN                          | 0          | 0          | 0          |
> | DYXX     | AREA PPP                          | 0          | 0          | 0          |
> | Total    | AREA PPP                          | 10         | 3          | 11         |
> +----------+-----------------------------------+------------+------------+------------+
> 13 rows in set (0.40 sec)

I need this return

> +----------+-----------------------------------+------------+------------+------------+
> | AREA_Cod | Area_name                         | 2024-04-01 | 2024-04-02 | 2024-04-03 |
> +----------+-----------------------------------+------------+------------+------------+
> | D7XX     | AREA AAA                          | 10         | 0          | 0          |
> | D8XX     | AREA BBB                          | 0          | 0          | 7          |
> | DEXX     | AREA CCC                          | 0          | 0          | 0          |
> | DJXX     | AREA DDD                          | 0          | 3          | 0          |
> | DKXX     | AREA EEE                          | 0          | 0          | 0          |
> | DLXX     | AREA FFF                          | 0          | 0          | 0          |
> | DNXX     | AREA GGG                          | 0          | 0          | 0          |
> | DUXX     | AREA HHH                          | 0          | 6          | 4          |
> | DVXX     | AREA LLL                          | 0          | 0          | 0          |
> | DWXX     | AREA MMM                          | 0          | 0          | 1          |
> | DXXX     | AREA NNN                          | 0          | 0          | 0          |
> | DYXX     | AREA PPP                          | 0          | 0          | 0          |
> | Total    |                                   | 10         | 9          | 15         |
> +----------+-----------------------------------+------------+------------+------------+
2

Your query is not valid as you select a column (name) which is not grouped. You should normally get an error.

3

I don’t have error on this query… thanks for reply

4

Then you have disabled the error in the mySQL settings which is not a good idea.

Read here to find more information about your problem

5

Fixing the error that Thallius is pointing out to you should also solve your problem. At least, if you do it the way I think it can be…

6

  1. First: I don’t have any errors

  2. Second: if I also group by the Area_name column, all the rows are duplicated in output for each Area_cod value, one row with values, the other row empty

7

First: You will eventually run into a server that will deny this query for violating its ONLY_FULL_GROUP_BY flag. We’re warning you now, so that you dont have to come back here when your query stops working. :slight_smile:

Second: Yeah that’s the Rollup doing what Rollup is supposed to do. Rollup will add rows to the table for each level of grouping - you’ve told it to have two levels of grouping, so it’s giving you a total for each value of the second level, and a total at the bottom for the top level of grouping. Try giving it a MAX(CASE statement instead (so that it’s applying an aggregate function to the thing to avoid the non-aggregated value in a group by select, and then CASE it off of the total row Area_cod value.)

(I’m not 100% sure the CASE functions on a rollup row, but making it an aggregate should at least make the rollup at least behave in a predictable way)

8

The other option is to make your own rollup row, by UNION’ing the same query again, but selecting “Total” and NULL as the corresponding columns and not grouping. Which is icky. But if you absolutely must have this total row come out of the database and not out of whatever you’re pushing this result into… eh.

9

excellent analysis as always, good sir or madam, as the case may be