Search code examples
sqlgroup-bysubquery

Condensing grouped count subquery

Is there a way to write this without a subquery, either in postgres or redshift? Maybe with a window function or something...

select count(*) from (select col1 from tbl1 group by 1 having count(*)>1) t

Solution

  • Yes, sort of. As you mention window functions run after group by but the column that the window function is running on needs to be in the select. So you will get a two column output while you can limit the rows with a "LIMIT 1".

    create table test as 
select 3 as col 
union all select 3
union all select 2 
union all select 2 
union all select 1 ;

select col,
  count(col) over () as cnt
from test 
group by 1 
having count(col)>1
limit 1;

    This does beg the question of why does this query need to be written in one level?