SQL Sorting by Non-Aggregate Field

Question

Hello everyone and thank for the help in advance. I have a SQL Server table that tracks incoming phone calls with the phone number and time entered. I want to create a query where I can also retrieve a count of the number of times a phone called, but sort the table by the call time. I tried the following:

Select Count(*) as occurrence_count, Caller, TimeEntered from tbl_Log_InboundCalls 
	Group By Caller
	Order By TimeEntered Desc

but I receive the error message "Column 'tbl_Log_InboundCalls.TimeEntered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." When I add TimeEntered as a Group By, the query does not return the correct data.

I can't figure out how to correct this problem. Any help would be appreciated.

Answer 1

in group by all columns not included in the group by must be a aggregate so there is only one row per column. You could use max() or min(). If you need a row for each time, then add to group by.

Answer 2

Try this too:

select count(*) over (partition by [Caller]) as [Count], [Caller], TimeEntered 
from tbl_Log_InboundCalls
order by TimeEntered desc