CODEWITHSUNDEEP
sqldatabasepostgresql
user8906123
user8906123

Reputation:

How to filter records by them amount per date?

i have a tablet 'A' that have a column of date. and the same date can be in a few records. I'm trying to filter the records where the amount of the records by day is less than 5. And still keep all the fields of the tablet.

I mean that if i have only 4 records on 11/10/2017 I need to filter all of this 4 records.

Upvotes: 1

Views: 33

Answers (2)

Hambone
Hambone

Reputation: 16377

Take Care's answer is good. Alternatively, you can use an analytic/windowing function. I'd benchmark both and see which one works better.

with cte as (
  select *, count(1) over (partition by date) as cnt
  from table_a
)
select *
from cte
where cnt < 5

Upvotes: 1

Take_Care_
Take_Care_

Reputation: 2144

So You can SELECT them basing at sub-query . In SUB-Query group them by this date column and then use HAVING with aggregated count to know how many in every date-group we have and then select all which have this count lesser than 5 ; 

SELECT * 
FROM A
WHERE A.date in (SELECT subA.date 
                   FROM A 
                   GROUP BY A.date 
                   HAVING COUNT(*) < 5 );

Upvotes: 1

Related Questions