Appearing and disappearing records
Create a table without any records:
create table tq84_empty (
val integer
);
The following query returns one record, although tq84_empty is still empty:
select
avg(val) avg_val,
const_num
from (
select
val,
42 const_num
from
tq84_empty
);
-- AVG_VAL CONST_NUM
-- ---------- ----------
-- 4
Adding the group by clause to the prior query makes that row disappear again:
select
avg(val) avg_val,
const_num
from (
select
val,
42 const_num
from
tq84_empty
)
group by
const_num;
--
-- no rows selected
Cleaning up:
drop table tq84_empty;