70-433 SQL Server GROUP BY ... WITH ROLLUP

GROUP BY ... WITH ROLLUP 

the GROUPING function (with the SELECT statement:

SELECT (CASE WHEN (GROUPING(Name) = 1) THEN 'TOTAL' ELSE Name END) as Name, Salary, SUM(Salary) AS Sal 

FROM Employee GROUP BY Salary, Name with ROLLUP

HAVING Salary ...

"if the CASE statement is not used with the GROUPING function, the value NULL will be displayed in the Name column for aggregate row"

I have two queries. One of them makes sense to me, the other don't. First one:

SELECT gender AS 'Gender', count(*) AS '#'
    FROM registrations
    GROUP BY gender WITH ROLLUP

That gives me this:

Gender       #
Female      20
Male        19
NULL        39

So, I get the count, and the total count. What I expected. Next one:

SELECT c.printable_name AS 'Country', count(*) AS '#'
    FROM registrations r
    INNER JOIN country c ON r.country = c.country_id
    GROUP BY country WITH ROLLUP

Country         #
Denmark         9
Norway         10
Sweden         18
United States   1
Uzbekistan      1
Uzbekistan     39

Same result. But why do I get Uzbekistan for the total??

Answer: Because you're not SELECTing the item that you're GROUPing BY. If you said:

GROUP BY c.printable_name

You'd get the expected NULL. However you're grouping by a different column so MySQL doesn't know that printable_name is taking part in a rollup-group, and selects any old value from that column, in the join of all registrations. (So it is possible you will see other countries than Uzbekistan.)

This is part of a wider problem with MySQL being permissive on what you can SELECT in a GROUP BY query. For example, you can say:

SELECT gender FROM registrations GROUP BY country;

and MySQL will happily pick one of the gender values for a registration from each country, even though there is no direct causal link (aka “functional dependency”) between country and gender. Other DBMSs will refuse the above command on the grounds that there isn't guaranteed to be one gender per country.(*)

http://stackoverflow.com/questions/659845/mysql-total-group-by-with-rollup-curiosity