MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE invoice (
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  amount DOUBLE DEFAULT NULL,
  name VARCHAR(100) DEFAULT NULL
);
INSERT INTO invoice (amount, name) 
VALUES (4.0, 'Michael'), (15.0, 'Bara'), (4.0, 'Michael'), (6.0, 'John');

Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM invoice;


id
amount
name


1
4
Michael

2
15
Bara

3
4
Michael

4
6
John


SELECT sum(amount), name
from invoice
group by name
having sum(amount) > 6


sum(amount)
name


15
Bara

8
Michael


SELECT sum(amount), name
from invoice
group by name
having (select name = 'John') -- refer parent data in subquery - ok!


sum(amount)
name


6
John


SELECT sum(amount), name
from invoice
group by name
having (select sum(amount) > 6) -- refer parent aggregate data in subquery - ok!


sum(amount)
name


15
Bara

8
Michael


SELECT sum(amount), name
from invoice
group by name
having (select sum(amount) > 6 from (select 1) t) -- still ok!


sum(amount)
name


15
Bara

8
Michael


SELECT sum(amount), name
from invoice
group by name
having (select v > 6 from (select sum(amount) v) t) -- the same, only the `sum()` expr
                                                    -- is moved inside the subsubquery

Unknown column 'amount' in 'field list'


SELECT sum(amount), name
from invoice
group by name
having (select v = 'John' from (select name v) t) -- even simple column reference
                                                  -- is broken

Unknown column 'name' in 'field list'