Need validation that interpretation for a Grouping Query is correct

Question

I am running the following query and at first it appears to give the sub totals for customers and shows by date each customers payment amounts only if that total for all payments is greater than $90,000.

SELECT 
    Customername, 
    Date(paymentDate), 
    CONCAT('$', Round(SUM(amount),2)) AS 'High $ Paying Customers'
FROM Payments 
JOIN Customers  
     On payments.customernumber = customers.customernumber
Group by customername, Date(paymentDate) WITH ROLLUP
having sum(amount)> 90000;

But upon looking at the records for Dragon Souveniers, Ltd. and Euro+ Shopping Channel is is actually showing the paydates that have amounts individually over $90000 as well as the subtotal for that customer as a rollup. For all other customers, their individual payment dates are not reported in the result set and only their sum is if it over $90000. For example Annna's Decorations as 4 payment records and none of them are over 90000 but her sum is reported as the value for the total payments in the query with the rollup. Is this the correct interpretation?

Answer 1

The HAVING clause work correct, It filters all records with a total no above 90000. It also does do this for totals.

When using GROUP BY .... WITH ROLLUP, you can detect the created ROLL UP lines by using the GROUPING() function.

You should add a condition in a way that the desired columns are not filtered.

Simple example:

select a, sum(a), grouping(a<3) 
from (select 1 as a 
      union 
      select 2 
      union select 3) x 
group by a<3 with rollup;

output:

+---+--------+---------------+
| a | sum(a) | grouping(a<3) |
+---+--------+---------------+
| 3 |      3 |             0 |
| 1 |      3 |             0 |
| 1 |      6 |             1 |
+---+--------+---------------+

this shows that the last line (with grouping(i<3) == 1) is a line containing totals for a<3.