SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select sqlite_version();


sqlite_version()


3.39.1


CREATE TABLE invoice (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  amount DOUBLE PRECISION 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');
✓
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

{
  "code": "SQLITE_ERROR"
}


SELECT sum(amount), name
from invoice
group by name
having (select v = 'John' from (select name v) t) -- with column name there is no issue


sum(amount)
name


6
John