Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Incorrect Syntax ?

Author  Topic 

s_meb
Starting Member

3 Posts

Posted - 2013-11-05 : 12:01:40
Hi All,

SQL 2012

Was hoping that someone could show me what I am missing. I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.

SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders)
Group BY custId

max

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-05 : 12:13:08
Try naming (assign alias) to subselect. Something like:

SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders) as a
Group BY custId
Go to Top of Page

s_meb
Starting Member

3 Posts

Posted - 2013-11-05 : 12:19:58
Thanks bitsmed - that was it.

Unfortunately I ran into this:
Operand data type datetime is invalid for avg operator.



max
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 12:45:44
[code]
SELECT custId, AVG(DATEDIFF(dd,priorDate,invDate)*1.0)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders) as a
Group BY custId
[/code]

the part in blue is required only if you want decimal part in result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

s_meb
Starting Member

3 Posts

Posted - 2013-11-05 : 15:55:43
Thanks visakh16 - worked perfectly . . . .

max
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 03:14:08
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -