CODEWITHSUNDEEP
sqloracle-database
Hemanth Tanna
Hemanth Tanna

Reputation: 29

Count records for first day of every month in a year

I have a table with 4 columns huge number of records. It has the following structure:

DATE_ENTERED    EMP_NAME     DATA       ORIGINATED
01-JAN-20        A          545454        APPLE

I want to calculate no of records for every first day of every month in a year

is there any way can we fetch the data for every first day of month.

Upvotes: 0

Views: 437

Answers (4)

Manjuboyz
Manjuboyz

Reputation: 7066

You can use something like this:

select * from your_table
where DAY(DATE_ENTERED) = 1
and DATE_ENTERED between '2020-01-01' and '2020-12-31'

for number of count use this:

select count(*) from your_table
where DAY(DATE_ENTERED)= 1
and DATE_ENTERED between '2020-01-01' and '2020-12-31'

UPDATE

select * from your_table where Extract(day FROM DATE_ENTERED) = 1 and DATE_ENTERED between '01-JAN-20 ' and '01-DEC-20 ';

this is how the data looks like:

enter image description here

For the list of records

select count(*) from your_table where Extract(day FROM DATE_ENTERED) = 1 and DATE_ENTERED between '01-JAN-20 ' and '01-DEC-20 ';

enter image description here

UPDATE-2

select EXTRACT(month from DATE_ENTERED) as Count, 
to_char(to_date(DATE_ENTERED, 'DD-MM-YYYY'), 'Month') from your_table 
where Extract(day FROM DATE_ENTERED) = 1 and DATE_ENTERED between '01-JAN-20 
'and '01-DEC-20 ' group by EXTRACT(month from DATE_ENTERED),  
to_char(to_date(DATE_ENTERED, 'DD-MM-YYYY'), 'Month');

Here is the output:

enter image description here

Upvotes: 0

Popeye
Popeye

Reputation: 35920

In oracle you can use TRUNC function on the date as follows:

SELECT TRUNC(DATE_ENTERED), COUNT(1) AS CNT
FROM YOUR_TABLE
WHERE TRUNC(DATE_ENTERED) = TRUNC(DATE_ENTERED, 'MON')
GROUP BY TRUNC(DATE_ENTERED, 'MON')

Please note that the TRUNC(DATE_ENTERED, 'MON') returns the first day of the month for DATE_ENTERED.

Cheers!!

Upvotes: 1

downernn
downernn

Reputation: 174

You mean something like 

SELECT COUNT(*) 
FROM Table 
WHERE DAY(DATE_ENTERED) = 1 AND
      YEAR(DATE_ENTERED) = Some_Year
GROUP BY DATE_ENTERED

You can also use DATE_ENTERED BETWEEN 'YYYY0101' and 'YYYY1231' (replace the YYYY with the year you want to retrieve data for) instead of YEAR(DATE_ENTERED) = Some_Year, if performance is an issue.

Upvotes: 0

A_kat
A_kat

Reputation: 1537

SELECT Year, Month, COUNT(*)
FROM
(
  SELECT 
  YEAR(DATE_ENTERED) Year
  MONTH(DATE_ENTERED) Month
  DAY(DATE_ENTERED) Day
  FROM your_table
  WHERE DAY(DATE_ENTERED) = 1
) A 
GROUP BY Year, Month

Generally WHERE DAY(DATE_ENTERED) = 1 will get you the records only for dates at the start of each month. Thus using Year and Month function you can group them by in order to get a count for each year and each month

Upvotes: 0

Related Questions