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'