Oracle Query Help

Question

I have this table

  ID          Date
  1   08/10/2009 11:20:00
  2   08/10/2009 12:06:06
  3   08/11/2009 13:03:10
  4   08/11/2009 01:20:00
  5   08/11/2009 15:41:23
  6   08/12/2009 14:22:20

I want to return:

Date          Count
08/10/2009     2
08/11/2009     3
08/12/2009     1

in PL/SQL. Any ideas?

Quassnoi · Accepted Answer · 2009-08-20 16:52:06Z

10

SELECT  TRUNC(date) AS CreatedDate, COUNT(*) AS DateCount
FROM    mytable
GROUP BY
        TRUNC(date)
ORDER BY
        CreatedDate

edited Aug 20, 2009 at 16:52

answered Aug 20, 2009 at 16:46

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Thomas Jones-Low Over a year ago

Doing this in PL/SQL requires creating a cursor from your query. Not very difficult at all.

akf Over a year ago

+1: you beat me to it, but i would have called it this_table.

Saobi Over a year ago

I don't understand how count(*) would correspond to the count of those entries that correspond to each distinct date.

Quassnoi Over a year ago

@Saobi: TRUNC(date) truncates the dates, leaving only the date portions. GROUP BY TRUNC(date) makes COUNT(*)'s for each group that shares the truncated values of the dates.

Saobi Over a year ago

Also, what if i want to display the date column as "CreatedDate" and Sort by that column ?

|

Collectives™ on Stack Overflow

Oracle Query Help

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related