> > > with QRY as (select C1.country, C1.state, sum(C1.population) > > from places C1 > > group by 1, 2 > > order by 3 DESC > > limit 10) > > > > select * from QRY > > union > > select 'others' as "country", '' as "state", sum(population) > > from places > > where not exists (select 1 from QRY where country = QRY.country and state > > = QRY.state) > > > > (not tested)
with QRY as ( SELECT country, state, sum(population) as st_pop FROM places GROUP BY country, state ) , u1 AS ( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC LIMIT 10 ) , u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY WHERE NOT EXISTS ( SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state) ) SELECT * FROM u1 UNION ALL SELECT * FROM u2 ; David J.
