In the video Mosh says that with the ROLLUP operator aliases don’t work. My solution pasted below is working for me. The problem seems to be a conflict between p.payment_method and the alias “payment_method” that MySQL cannot resolve. Can anyone confirm that this true?
solution to exercise ROLLUP operator
USE sql_invoicing;
SELECT
pm.name AS pay_method,
SUM(p.amount) AS total
FROM payments p
JOIN payment_methods pm ON
p.payment_method = pm.payment_method_id
GROUP BY pay_method WITH ROLLUP
I think you are right. Unfortunately Mosh has so much entries in his Action Output view that we can’t see the actual error he received.
It might have been such a misleading error message (that stupid machines often produce in such edge cases) that he missed the forest for the trees. Or MySQL added support for aliases later (I can execute his original query without any error) but I doubt that - if they work with a plain GROUP BY clause I don’t see a reason why they shouldn’t work with an added WITH ROLLUP modifier.
Just for curiosity: Does his original query work on your MySQL version? If not, could you post the error message?
Yes Mosh’s original solution works. But when going through the exercise I stumbled upon the problem he described without having seen his solution. From the error message I was getting I gave the solution above and thought I’d post it here to see what’s going on.
I’m using MySQL workbench version 8.0.22 and
mysql Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)
on Ubuntu 20.04 LTS.