Can't get a GROUP BY to work - what am I doing wrong?

[suchil00]

I have a table ORDER_HEADER with the following columns - ORDER_NUMBER, ORDER_DATE and ORDER_TOTAL.
I want a report of the order_totals by each day. I have hard coded a sample time period for testing

I have attached the code and for the life of me I can't get it to group. The output returns all the orders with their order total.

[Dan_Bracuk]

What happens when you cfdump your query?

[Newsgroup_User]

does the order_date column in your order_header table include time part
too? that would make all (well, most) of your dates different... if so,
you need to extract just the date part from order_date column inside
your query and compare that to your selected dates...

--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[suchil00]

Thanks - saw your reply after my other post. You are correct. I tried the TO_CHAR in the query. Check this code now - error says - can't find "FROM" where expected

[cf_dev2]

"Date" is a reserved word in most databases. Try changing the alias

> GROUP BY order_date
I'm not familiar with oracle but you may need to GROUP BY TO_CHAR(order_date, 'MM-DD-YYYY') instead

[suchil00]

OK - now I am selecting on the date part with the attached code. Still no GROUPING? Have also attached part of the CFDUMP

[Dan_Bracuk]

quote:

---

Originally posted by: freezer9
OK - now I am selecting on the date part with the attached code. Still no GROUPING? Have also attached part of the CFDUMP

---

You also have to group by the date part. Some dbs allow you to group by the alias in the select clause. Some make you put the function in the group by clause.

[suchil00]

Thank you guys - that is exactly what it was. Had to GROUP BY the funtion.

[Newsgroup_User]

if i were you, i would not try and convert your dates to anything else -
dates should be dates. what i would do, is extract just the date part
out of datetime value in your order_date column (i.e. in MySQL that is
DATE() function).

another thing i would do differently is use <cfqueryparam> tag and
createdate() cf function in your WHERE clause to set the comparisson
dates: <cfqueryparam cfsqltype="cf_sql_date" value="#createdate(2006, 8,
6)#">
--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[suchil00]

It gives me the same 616 records - which is basically all the orders for that time period

The date is stored with the hrs, mins and secs. Could that be the reason that the GROUPING is not occurring?