Have Geek Brain

How can I group my columns in SQL?

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
+1


source to share


5 answers


[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
+6


source


Try

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


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

+8


source 


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 changed GROUP BY

to include the actual expression for [Time]

and not the column alias (as this cannot be used in GROUP BY

, only ORDER BY

)

+1


source


It looks like Date is a column, but that is a keyword and is not quoted. Probably the problem (not tested):

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
0


source


If you don't want to iterate over the date conversion (sometimes the computation is a little more intensive than the simple conversion), you can use something like this:

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

Note that you must supply the UG name for the internal selection, and that this will be most likely and in most cases less efficient than repeating the transform expression in the original grouping. Also your expression will most likely change ... But this way you know it is possible.

0


source






More articles:


All Articles