0

For a long time I am struggling with the following subject: I want to count datepart values. I use SQL Compact Edition 4.0 and have no idea on how to get the following:

select datepart(week, CreateDate) as Week, count(*) from tblOrders 
where CreateDate>'12 April 2010' and CreateDate<'25 June 2011'

This does not work obviously, but to give you an idea what I want to get as the result is: - 2 columns,

  • one called "week" - that would be a week number
  • in the second column - how many orders I had per week

Thanks in advance,

Pete

Pete
  • 77
  • 3
  • 10

1 Answers1

1

You'll need to add a Group By to make the query syntax correct.

select datepart(week, CreateDate) as Week, count(*) 
from tblOrders  where CreateDate>'12 April 2010' and CreateDate<'25 June 2011'
group by datepart(week, CreateDate)

Does that help?

Adrian
  • 207
  • 2
  • 13
  • Thanks very much, this is exactly what I wanted. By a mistake I used order by ... ;) Thank you anyway! – Pete Sep 02 '11 at 10:03