I have two tables, one for Employees and the second for records. I want to get total entries for each employee and order the results by max total entry like:
Daniel 3
David 1
tblEmployee:
EID Name
1 Daniel
2 David
tblEntry:
ID Column1 EID
1 XX 1
2 XX 1
3 XX 2
4 XX 1
try this:
select emp.EID,emp.Name,COUNT(etr.EID)
as total_entries from Employee emp join Entry etr
on emp.EID=etr.EID
group by emp.EID,emp.Name
You must use group by
select count(*) from tblEmployee ee, tblEntry e where ee.eid = e.eid group by ee.name
There are several variations on this, and you don't say what version of SQL Server you're using, but I like doing it this way:
;
using A
as (
select EID
, RecordCount = COUNT(*)
from tblEntry
group by
EID
)
select a.EID
, e.Name
, a.RecordCount
from A
join tblEmployee e
on A.EID = e.EID
order by
RecordCount desc
I like doing it this way rather than joining and then summarizing because you only have to group on the minimum number of fields. EID in tblEntry is likely to already have an index on it, while Name in tblEmployee may not.
