MySQL Forums
Forum List  »  Docs

GROUP BY + ROLLUP + IFNULL?
Posted by: Jack Repenning
Date: October 16, 2009 02:25PM

In comments to the GROUP BY Modifiers page (11.11.2), several people discuss using IFNULL() in the select clause to enhance the presentation of the results of ROLLUP. This is not working for me. Am I doing it wrong? Is it version-specific? Does my query involve something else that invalidates it?

They say, for example:
> SELECT IFNULL(year,"Total") as year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
> returns:
> +-------+-------------+
> | year | SUM(profit) |
> +-------+-------------+
> | 2000 | 4525 |
> | 2001 | 3010 |
> | Total | 7535 |
> +-------+-------------+


But I find that
select count(1),
IFNULL(user,"Total") as user,
IFNULL(project,"Total") as project
from access
natural join cmddim
natural join userdim
natural join projectdim
natural join actiondim
where action = 'VC'
and cmd = 'PUT'
and user != '-'
group by 3,2 with rollup;

returns

+----------+----------------+-------------------------------------------+
| count(1) | user | project |
+----------+----------------+-------------------------------------------+
| 19 | bobtarling | argouml |
| 1 | mvw | argouml |
| 20 | NULL | argouml |
...

I expected that NULL to be replaced by "Total". All the other NULLs throughout the result set, both columns, are likewise still "NULL", not "Total".

One comment suggests COALESCE() rather than IFNULL() (on a standards-conformance argument). I tried COALESCE() as well, with the same lack of success as IFNULL().

I'm using Server version: 5.1.37 MySQL Community Server (GPL)
on Mac OS X, and a case-insensitive file system (hence MySQL is case-insensitive about many things as well)

Options: ReplyQuote


Subject
Views
Written By
Posted
GROUP BY + ROLLUP + IFNULL?
11678
October 16, 2009 02:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.