Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value.
Let's see a small example. I know the values are too small for the data presented, but just to keep it readable.
- One that appears late in the result set.
- One that has a higher value for SUM(visit) field. This is arguable in general as there might be negative values, for our example we can say that's impossible.
While using ROLLUP, you may further like to consider few points.
- ROLLUP and ORDER BY are mutually exclusive, they cannot be used in the same statement.
- If you are using LIMIT clause along with ROLLUP, remember LIMIT clause is applied after ROLLUP, so it may include the aggregated rows.
- In MySQL documentation, it is mentioned that "Because the
NULLvalues in the super-aggregate rows are placed into the result set at such a late stage in query processing, you cannot test them as
NULLvalues within the query itself.". You can see in the above queries, we are actually testing this value to be replaced by TOTAL_* string. Also if your data itself does not have NULL values in it, it works wonders. Look at the output below, I have removed the row inserted above.