Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

datediff and group by

Posted on 2011-02-11
3
Medium Priority
?
348 Views
Last Modified: 2012-05-11
Hi experts,

The attached stored proc is supposed to create a resultset that contains two columns, one showing the number of orders and the second showing the timetaken.

However, it errors stating that timetaken is an invalid column name!?

What am I doing wrong?

Help appreciated.
SELECT COUNT(o.orders_id) AS ordercount, (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp)) AS timetaken
FROM orders O, dbo.tblPS_orders_status_history OSH WHERE
o.orders_status = 4
AND o.paymentMethodID = 1
AND o.orders_statusID = OSH.orders_status_history_id
AND DATEDIFF(DAY, o.date_purchased, GETDATE()) < 31
GROUP BY timetaken
ORDER BY timetaken

Open in new window

0
Comment
Question by:jammy-d0dger
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34872140
indeed. while you can ORDER BY an alias, you cannot GROUP BY ...
SELECT COUNT(o.orders_id) AS ordercount, (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp)) AS timetaken
FROM orders O, dbo.tblPS_orders_status_history OSH WHERE
o.orders_status = 4
AND o.paymentMethodID = 1
AND o.orders_statusID = OSH.orders_status_history_id
AND DATEDIFF(DAY, o.date_purchased, GETDATE()) < 31
GROUP BY  (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp))
ORDER BY timetaken

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34872154
Alias cannot be used in the same query
Instead try its values

SELECT COUNT(o.orders_id) AS ordercount, (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp)) AS timetaken
FROM orders O, dbo.tblPS_orders_status_history OSH WHERE
o.orders_status = 4
AND o.paymentMethodID = 1
AND o.orders_statusID = OSH.orders_status_history_id
AND DATEDIFF(DAY, o.date_purchased, GETDATE()) < 31
GROUP BY (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp))
ORDER BY (DATEDIFF(DAY,  o.date_purchased, osh.orders_status_history_timestamp))
0
 

Author Closing Comment

by:jammy-d0dger
ID: 34872196
Perfect, thanks Angellll.  Seems pretty lame that you can use it for order by but not group by?  Thanks for the work-around, working perfectly.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question