CODEWITHSUNDEEP
sqloracle-database
barteloma
barteloma

Reputation: 6875

sql query group by day of month

I have a record table that icludes dates of rows created. (oracle db)

ID    City       CreateDate
1     city-1     12.12.2017
1     city-2     13.12.2017
1     city-1     13.12.2017
1     city-3     12.12.2017
....
....

I want to create a daiy report in a month. For example City-1 report by days in December

Day    Count
1      10
2      80
3      60
4      10
...
30     11

Upvotes: 0

Views: 367

Answers (2)

BSants
BSants

Reputation: 91

I think you can use extract with count functions: 

SELECT EXTRACT(day FROM CreateDate) "Day",
      COUNT(CreateDate) "Number of Reports"
      FROM yourTableName
      GROUP BY EXTRACT(day FROM CreateDate)
      ORDER BY "Number of Reports" ASC;

Upvotes: 2

Shateel Ahmed
Shateel Ahmed

Reputation: 1334

If I understood it correctly, the following query will generate the report you wanted for December.

SELECT EXTRACT(day FROM CreateDate) "Day", COUNT(*) "Count" FROM your_record_table WHERE EXTRACT(month FROM CreateDate) = 12 GROUP BY EXTRACT(day FROM CreateDate) ORDER BY EXTRACT(day FROM CreateDate);

Upvotes: 1

Related Questions