How can I group by my columns in SQL?

Question

How can I group by Time? I tried this, but it gives the error "Invalid column name 'Time'.":

select Count(Page) as VisitingCount, CONVERT(VARCHAR(5), Date, 108) as [Time]
from scr_SecuristLog   
where Date between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in (select USERNAME from scr_CustomerAuthorities)  
group by [Time] order by [VisitingCount] asc

Answer 1

Try

GROUP BY CONVERT(VARCHAR(5),Date, 108)

Always make sure you group by everything in your select clause that does not have an aggregate function around it.

Answer 2

select Count(Page) as VisitingCount,CONVERT(VARCHAR(5),Date, 108) as [Time] from scr_SecuristLog   
where Date between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by CONVERT(VARCHAR(5),Date, 108) order by [VisitingCount] asc

I modified the GROUP BY to include the actual expression for [Time], rather than the column alias (since this can't be used in the GROUP BY, only the ORDER BY)

Answer 3

[Time] is a column alias. Try

SELECT 
      COUNT(Page) AS VisitingCount
    , CONVERT(VARCHAR(5),Date, 108) AS [Time] 
FROM
    scr_SecuristLog   
WHERE
    Date BETWEEN '2009-05-04 00:00:00' AND '2009-05-06 14:58'  
    AND
    [user] IN (
       SELECT 
        USERNAME             
       FROM
        scr_CustomerAuthorities 
       )  
GROUP BY
    CONVERT(VARCHAR(5),Date, 108) 
ORDER BY
    [VisitingCount] ASC

Answer 4

It looks like Date is a column here, but it's a keyword and not quoted. Perhaps that's the issue (not tested though):

select Count(Page) as VisitingCount,CONVERT(VARCHAR(5),[Date], 108) as [Time] from scr_SecuristLog   
where [Date] between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by [Time] order by [VisitingCount] asc

Answer 5

If you don't want to repeat the date conversion (Sometimes calculation is a bit more intensive than simple conversion), then you can use something like this:

select * 
from ( select Count(page) .., ... As [Date] from ... where ...) UG
group by UG.[Date]

Notice that you have to give the 'UG' name to internal select and that this will be most likely and in most cases less efficient than repeating the conversion expression in original grouping. Also your whole expression is likely to change... But so just you know that it is possible.