Hello,

I have a table with columns id and start_time:

id | start_time
-----+---------------------
96 | 2010-11-24 09:30:00
97 | 2010-11-24 10:30:00
95 | 2010-11-25 08:00:00

I would like to write a query that will group for each *date* in the
start_time column the row count... something like this:

start_time | count
--------------------+-------
2010-11-24 09:30:00 | 2
2010-11-25 08:00:00 | 1

Of course, there is no date column in the table, otherwise it would be
too easy

Any help will be appreciated!

Thanks

Jim

On 2010-11-29 20:20, jmb (AT) nospam (DOT) com wrote:
as (values (96,'2010-11-24 09:30:00')
,(97,'2010-11-24 10:30:00')
,(95,'2010-11-25 08:00:00')
)
Select count(*)
,cast(TS as date)
from T1
group by cast(TS as date)
;
-----------------------------
count ts
1 2010-11-25
2 2010-11-24

/dg

jmb (AT) nospam (DOT) com wrote on 29.11.2010 20:20:
If you can live with the fact that the hour is removed, this is pretty simple:

SELECT cast(start_time as date), count(*)
FROM your_table
GROUP BY cast(start_time as date)

Thomas

On 2010-11-29 20:20, jmb (AT) nospam (DOT) com wrote:
I don't have a psql installation at hand, so I can't verify, but you
could try something like:

select distinct count(*) over (partition by cast(ts as date))
,min(TS) over (partition by cast(ts as date))
from T

For a large table, I don't think it will be very efficient though.

/Lennart

On 2010-11-29 21:59, Lennart Jonsson wrote:
[...]
Probably clearer like:

select start_time, count(*) from (
select min(TS) over (partition by cast(ts as date)) as start_time
from T
) as X
group by start_time;

/L

On 2010-11-29 22:06, Lennart Jonsson wrote:
[...]
without OLAP function:

select start_time, count(*) from (
select cast(ts as date) as dt, min(TS) as start_time
from T group by cast(ts as date)
) as X
join T
on dt = cast(ts as date)
group by start_time

/L

On 11/29/2010 09:08 PM, Thomas Kellerer wrote:
The loss of hours is easily fixed. One could do

SELECT min(start_time) as start_time, count(*)
FROM your_table
GROUP BY cast(start_time as date)

Other aggregation functions can be used as well, but other than min()
and max() do not seem very useful to me.

Kind regards

robert

On 11/29/2010 10:17 PM, Lennart Jonsson wrote:
Why so complicated? Why not just

SELECT min(start_time) as start_time, count(*)
FROM your_table
GROUP BY cast(start_time as date)

?

Kind regards

robert

On 2010-11-29 22:44, Robert Klemme wrote:
[...]
clearly better. I claim my right to ignore the power of the group by
operator ;-)

/Lennart

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

that's it!
Thank you very much Thomas, and thanks to the other guys who spent some
time to help and answered to my question.

Jim

--