SQL Count for a Date Column

Question

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.

Crocodile

In you example I assume you mean September and not November. Can you show us an example of the output you would like to see? – Agnola

The output must be the count, and ideally the month. For example, (AUG, 14) if there are 14 records for August of the same year. Obviously. – Crocodile

Please add 'oracle' tag to your question :-D – Coaming

Answer 1

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

Pneumonic

ORA-00904: "MONTH": invalid identifier – Crocodile

what docs? MONTH is not an Oracle function. anyway, I'm feeling generous today so I've edited your answer :) – Syndicate

Thanks a lot, I wasn't aware that he was using Oracle. – Pneumonic

Answer 2

create table x (date_col date);

insert into x values (date '1998-09-02');
insert into x values (date '1998-09-03');
insert into x values (date '1998-09-12');
insert into x values (date '1998-09-14');
insert into x values (date '1998-10-02');
insert into x values (date '1998-11-21');

SELECT TRUNC(date_col,'MM'), count(*)
FROM x
GROUP BY TRUNC(date_col,'MM')
HAVING count(*) > 3;

Kent

Answer 3

So if 3 coloums contain 1999-01-xx you want to get that fetched ?

SELECT YEAR(date), MONTH(date) 
FROM table GROUP BY YEAR(date), MONTH(date) 
HAVING COUNT(*) > 3

If you need all the rows that contain the upper result it should look something like that

SELECT * FROM table 
INNER JOIN (
   SELECT YEAR(date) as y, MONTH(date) as m 
   FROM table GROUP BY YEAR(date), MONTH(date) 
   HAVING COUNT(*) > 3
) as virtualTable 
ON virtualTable.y = YEAR(date) AND virtualTable.m = MONTH(date)

Fai

Answer 4

Ideally you should create a stored procedure that accepts the two criteria you need, Month(integer) and limit(integer)

In a parameterized procedure that executes the following

 SELECT MONTH(Date) AS TheMonth, COUNT(MONTH(Date)) AS TheMonthCount
    FROM MyTable
    GROUP BY MONTH(Date)
    HAVING (COUNT(MONTH(Date)) > @limit) AND (MONTH(Date) = @month)

To also output the relevant month you could use the following

SELECT CAST(YEAR(Date) AS NVARCHAR) + '.' + 
       CAST(MONTH(Date) AS NVARCHAR) AS 'The  ', 
MONTH(Date ) AS TheMonth, COUNT(MONTH(Date)) AS TheMonthCount
    FROM Audit_Entry
    GROUP BY MONTH(Date), 
         CAST(YEAR(Date) AS NVARCHAR) + '.' +            
         CAST(MONTH(Date) AS NVARCHAR)
    HAVING (COUNT(MONTH(Date)) > @limit) AND (MONTH(Date) = @month)

Coaming

Answer 5

This example will help :

create table d1
( event_date date, event_description varchar2(100));

insert into d1 values (sysdate,'Phone Call');
insert into d1 values (sysdate,'Letter');
insert into d1 values (sysdate-50,'Interview');
insert into d1 values (sysdate-50,'Dinner with parents');
insert into d1 values (sysdate-100,'Birthday');
insert into d1 values (sysdate-100,'Holiday');
insert into d1 values (sysdate-100,'Interview');
insert into d1 values (sysdate-100,'Phone Call');

commit;

select * from d1;

EVENT_DATE                EVENT_DESCRIPTION                                                                                    
------------------------- ----------------------------------------------- 
04-MAR-10 14.47.58        Phone Call                                                                                           
04-MAR-10 14.47.58        Letter                                                                                               
13-JAN-10 14.47.58        Interview                                                                                            
13-JAN-10 14.47.58        Dinner with parents                                                                                  
24-NOV-09 14.47.58        Birthday                                                                                             
24-NOV-09 14.47.58        Holiday                                                                                              
24-NOV-09 14.47.58        Interview                                                                                            
24-NOV-09 14.47.58        Phone Call                                                                                           
8 rows selected

You can see that Nov-09 is the only month which more than 3 events.

Referring back to your original question, which was And return if for one month, that count sums more than 3. The following SQL aggregate will work.

select trunc(event_date,'MONTH'),count('x') from d1 
having count('x') > 3 group by trunc(event_date,'MONTH') 

Alternatively, use to_char to convert the Date type to a Char with a MON-YYYY picture as follows :

select to_char(trunc(event_date,'MONTH'),'MON-YYYY') month,
    count('x') no_of_occurances from d1 having count('x') > 3 group trunc(event_date,'MONTH') 

Nealon

Oops, I hadn't see Gary's post in amongst everything else. I voted Gary up, ignore this post as it's exactly the same as Gary but I posted 12 hours later. Sorry Garry. – Nealon

Answer 6

This should work for mysql and mssql:

SELECT MONTH(date), Sum(MONTH(date))
FROM table
GROUP BY date
HAVING Sum(MONTH(date)) > 3

Tsarina

Answer 7

I am not sure which database you are using.

In MySQL query will be similar to the method proposed by @THEn

On SQL server you have other interesting possibilities.

Read the this article for more details.

Cork

Answer 8

You could use Oracle's EXTRACT method :

select theMonth, sum(monthCount)
from (
  select 
    extract(MONTH FROM t.theDateColumn) as theMonth,
    1 as monthCount
  )
group by theMonth
having sum(monthCount) >= 3

I don't have an Oracle database at hand at the moment, so this code may not work as is - I apologize for this.

Aleut

Answer 9

Could be wrong but a guess:

SELECT SUM(date) FROM table
GROUP BY date where SUM(date) > 3

Dupuis

No. The sum of the dates greater than 3 must also correspond to the same month. – Crocodile