ROLLUP returning incorrect value

Question

Stuck on this for a bit today and was wondering can anyone suggest what's going wrong. Here is my code:

select g.game_name, r.rental_id, sum(if(datediff(r.return_date, r.due_date) > 0, datediff(r.return_date, r.due_date) * g.overdue_charge_per_day, 0)) Overdue_Charges
from rental as r
left join game as g
on r.game_id = g.game_id
where month(r.return_date)=month(now())
group by rental_id
with rollup;

This displays the following:

GTA V 12 4.00

Tony Hawk 13 15.00

Tony Hawk null 19.00

So it totals 4 & 15. However I don't want it to display "Tony Hawk" twice. Can I replace this with Grand Total? Maybe it's a joining issue and the second Tony Hawk should also be null. Thanks

Answer 1

This behaviour is explained in the "GROUP BY Modifiers" section of the documentation:

MySQL permits a column that does not appear in the GROUP BY list to be named in the select list. In this case, the server is free to choose any value from this nonaggregated column in summary rows, and this includes the extra rows added by WITH ROLLUP. For example, in the following query, country is a nonaggregated column that does not appear in the GROUP BY list and values chosen for this column are indeterminate:

mysql> SELECT year, country, SUM(profit)
    -> FROM sales GROUP BY year WITH ROLLUP;
+------+---------+-------------+
| year | country | SUM(profit) |
+------+---------+-------------+
| 2000 | India   |        4525 |
| 2001 | USA     |        3010 |
| NULL | USA     |        7535 |
+------+---------+-------------+

This behavior occurs if the ONLY_FULL_GROUP_BY SQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal because country is not listed in the GROUP BY clause.

Now, to show another value "Grand Total", you could use a wrapping query with an if like this:

select    if(rental_id is null, 'Grand Total', game_name) game_name,
          rental_id,
          Overdue_Charges
from      ( 
        select    game_name 
                  r.rental_id, 
                  sum(if(datediff(r.return_date, r.due_date) > 0, 
                         datediff(r.return_date, r.due_date) * g.overdue_charge_per_day,
                         0)) Overdue_Charges
        from      rental as r
        left join game as g
               on r.game_id = g.game_id
        where     month(r.return_date)=month(now())
        group by  rental_id
        with rollup) sub;