Group by a function

[mike777]

Hello all.
I am trying to take a date time column and apply a data parameter to it, and then group on the date parameter.
Like:

select c.calldate
  from calls c
 where cast(c.calldate as date)='06/03/03'
group by cast(c.calldate as date)
order by cast(c.calldate as date)

The problem is with using the cast function in the group by clause. I get an error there. If I don't extract the date from the date time, then the group by has no effect, since every record has a different time.
Any help is appreciated.
-Mike

 

[Oppenhiemer]

HI..

I may be (and probably am) wrong here - but I think the "Group By" clause is only valid on Aggregate or Collated data. So it is invalid in this case.

Would not the "Order By" clause do exactly what you need ? If its ordering the results on Date/Time values - they should be "grouped" together anyway ?

Opp.

 

[mike777]

Hi Opp.
If you group on a date-time column, you get a distinct row for every date time. For example, 06/03/03 2:51:51 is distinct from 2:51:52. If I am only interested in the 06/03/03 portion of the date time value, I would need to group on a function that extracts just that portion of the column value.
This way,
select cast(saledatetime as date)as SaleDate, count(0) as No_Each
from sales_data
group by cast(saledatetime as date)
would return only:
06/03/03,2
as opposed to
select saledatetime,count(0)
from sales_data
group by salesdatetime
which would return
06/03/03 2:51:51,1
06/03/03 2:51:52,1

Does that make sense?

It doesn't appear that this database allows grouping on function values, but I am not sure, so I started this thread.

Thanks.

-Mike