2 Replies - 7412 Views - Last Post: 09 January 2013 - 07:45 AM Rate Topic: -----

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 485
  • View blog
  • Posts: 3,250
  • Joined: 12-January 10

sum /avg an alias column

Posted 07 January 2013 - 08:03 AM


select sum(days_in_received_and_billed)/COUNT(days_in_received_and_billed) as avg_stuff


from
(

select
datediff(day, ( select top 1  t_order_detail_event.dte_created
 from t_order_detail_event
 where t_order_detail_event.business_work_flow_event_ID = 59 and t_order_detail_event.order_detail_ID = 1756117) ,
  (select top 1  t_order_detail_event.dte_created
 from t_order_detail_event
 where t_order_detail_event.business_work_flow_event_ID = 101 and t_order_detail_event.order_detail_ID = 1756117) )as days_in_received_and_billed,
) as data_s



the errors i am getting


Quote

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.


and

Quote

No Column was specified for column 2 of 'data_s'



i need to get the average of the sum of the date diff as specified above


thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: sum /avg an alias column

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: sum /avg an alias column

Posted 07 January 2013 - 11:16 AM

Well, you have a stray trailing comma at the end of line 13. That could cause problems. Also, if you're trying to get the average of a single, why not just use the AVG() aggregate function?
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 485
  • View blog
  • Posts: 3,250
  • Joined: 12-January 10

Re: sum /avg an alias column

Posted 09 January 2013 - 07:45 AM

i did find the "," right after I posted this and you cant use avg() on a mulitple aliased row/column data pull


my solution was I placed this query into a crystal report and forced the report to do the avg

This post has been edited by DarenR: 09 January 2013 - 07:46 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1