Hello and thanks for reading this,
I am trying to group a timestamp column by date only
The following statement does not work since the column date_entered includes
both date and time so the GRUP BY does not group the proper way I wish it
to
SELECT SUM(subtotal), count(*), date_entered FRM orders WHERE date_entered
12/24/2004 GRUP BY date_entered RD
ER BY date_entered;"
Can anyone help on how to modify this so I can group by DATE NLY ( all
sales per day )
Thank you ,
Sean.

"News" <born2net@sbcglobal.netwrote in message
news:i_jrf.47349$6e1.698@newssvr14.news.prodigy.co m
I am trying to group a timestamp column by date only

You don't state which RDBMS product you're using, but many SQL
implementations allow you to use expressions in the GRUP BY clause, and may
include a built-in function for extracting the date value from a timestamp
field. For example in MySQL:

GRUP BY DATE(date_entered)

Refer to the documentation for the RDBMS product and version you're using to
confirm whether this feature is supported, and also what the name of the
function you can use to get the date portion of the value.

In standard SQL language, you may have support for the EXTRACT function,
instead of a DATE function.

Regards,
Bill K.

News wrote:

I am trying to group a timestamp column by date only
The following statement does not work since the column date_entered includes
both date and time so the GRUP BY does not group the proper way I wish it
to
>
>
>
SELECT SUM(subtotal), count(*), date_entered FRM orders WHERE date_entered
12/24/2004 GRUP BY date_entered RD
ER BY date_entered;"

SELECT SUM(subtotal), count(*), CAST(date_entered AS DATE)
FRM orders
WHERE date_entered 12/24/2004
GRUP BY CAST(date_entered as DATE)
RDER BY CAST(date_entered as DATE);

If you can't use the CAST within GRUP BY, use a Derived Table instead:

SELECT SUM(subtotal), count(*), d
FRM
(
SELECT subtotal, CAST(date_entered AS DATE) as d
FRM orders
WHERE date_entered 12/24/2004
) dt
GRUP BY d
RDER BY d;

Dieter

Perfect Dieter, worked GREAT
I am using Postgresql and it did the job
Thank you for all the help,
Sean - HeliHobby.com

"Dieter Noeth" <dnoeth@gmx.dewrote in message
news:dolnq1$isk$1@online.de
News wrote:
>
>I am trying to group a timestamp column by date only
>The following statement does not work since the column date_entered
>includes both date and time so the GRUP BY does not group the proper way
>I wish it to
>>
>>
>>
>SELECT SUM(subtotal), count(*), date_entered FRM orders WHERE
>date_entered 12/24/2004 GRUP BY date_entered RD
>ER BY date_entered;"
>
SELECT SUM(subtotal), count(*), CAST(date_entered AS DATE)
FRM orders
WHERE date_entered 12/24/2004
GRUP BY CAST(date_entered as DATE)
RDER BY CAST(date_entered as DATE);

If you can't use the CAST within GRUP BY, use a Derived Table instead:

SELECT SUM(subtotal), count(*), d
FRM
(
SELECT subtotal, CAST(date_entered AS DATE) as d
FRM orders
WHERE date_entered 12/24/2004
) dt
GRUP BY d
RDER BY d;

Dieter