Search code examples
sqloracledate-manipulation

SQL Count for a Date Column

I have a table that containts a set of columns one of it is a Date column.

I need to count how many occurrences of the values of that column refer to the same month. And return if for one month, that count sums more than 3.

For example:

____________________
| DATE   |  ....    |
---------------------
1998-09-02
1998-09-03
1998-10-03
1998-10-04

This must return no value. Because it doesn't have the necessary number of repetitions.

But this it does:

____________________
| DATE   |  ....    |
---------------------
1998-09-02
1998-09-03
1998-09-12
1998-09-14
1998-10-02
1998-11-21

For the november month.

Is for an Oracle DB.

Solution 
  • SELECT
 COUNT(date)
 , TRUNC(DATE,'MON')
FROM TABLE
GROUP BY TRUNC(DATE,'MON')
HAVING COUNT(DATE) > 3