Ordering by a Derived Field

PeterErhard
PeterErhard used Ask the Experts™
on
I currently have the following query which gives the correct result:
      
      select
      datename(month,log_date) + ' ' + datename(year,log_date) as CalcDate,
      COUNT(*)
from
      logging
group by
      datename(month,log_date) + ' ' + datename(year,log_date)
                                           
However, how can I "order by" so it appears in logical month by month order (i.e. Jan 2012, Feb 2012, Mar 2012, etc)?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Try:

select 
      datename(month,log_date) + ' ' + datename(year,log_date) as CalcDate, 
      COUNT(*)
from 
      logging
group by 
      datename(month,log_date) + ' ' + datename(year,log_date)
order by
      log_date

Open in new window

Author

Commented:
Thanks, but get:

Msg 8127, Level 16, State 1, Line 10
Column "log_date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
select
      datename(month,log_date) + ' ' + datename(year,log_date) as CalcDate,
      COUNT(*)
      year(log_Date)
      month(log_date)
from
      logging
group by
      datename(month,log_date) + ' ' + datename(year,log_date)
order by
      year(log_date)
      month(log_Date)

Author

Commented:
Solved it myself.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial