Unusual SQL query, giving "Invalid column name" error

[MrPaul]

Howdy,

 

The database I am working with has a table of transactions (called MunitTransaction) and I wish to retrieve the total number of transactions, and their total monetary value, between two dates. However, I want them grouped into groups.

 

For example, in the following query, I want to retrieve information about today's transactions, with the transactions grouped into hourly chunks:

 

SELECT
   COUNT( [MunitID] ) AS [TransCount],
   SUM( [Value] ) AS [Revenue],
   (DATEDIFF(hh, '2006-07-14 00:00:00', [DateCreated])) AS [interval]
FROM
   [MunitTransaction]
WHERE
   (   [MunitTransaction].[DateCreated]
       BETWEEN
       '2006-07-14 00:00:00'
       AND
       '2006-07-15 00:00:00' )
GROUP BY
   [interval]
ORDER BY
   [interval]

 

The error I am getting is Invalid column name 'Interval'. I have tried a few things but they make no difference, or produce other errors. If anybody has an idea of how to do this, I would appreciate it if you could post back.

 

Thanks peeps. :cool:

[IUnknown]

Try by replacing [interval] with (DATEDIFF(hh, '2006-07-14 00:00:00', [DateCreated])) in the Group By and Order By parts.

[Puiu]

use just

...

group by DateCreated

order by DateCreated