How do I use 'id' results of a COUNT, GROUP BY, and HAVING query to perform another query-CodePudding

I have a table with 32 ids and each id should have 31 rows (31 dates). I want to select the ids and all its dates only for ids with less than 31 rows (31 dates).

I've tried this and a few other things:

SELECT id, date
FROM daily_activity
WHERE id IN(SELECT id, COUNT(id) AS num_days
            FROM daily_activity
            GROUP by id
            HAVING num_days < 31)"
ORDER BY id, date

Thanks

CodePudding user response：

One solution would be to use count as a window function

with ids as (
    select *, Count(*) over(partition by id) tot
    from mytable
)
select id, date
from ids
where tot<31

CodePudding user response：

You can try this maybe it'll work for you:

SELECT id, date
FROM daily_activity
where( select COUNT(daily_activity2.id) 
            FROM daily_activity daily_activity2 
             where daily_activity.id=daily_activity2.id 
             group by daily_activity2.id ) < 31
ORDER BY id, date