Joining table get the first data on first table if duplicate

Hi i have table1 and table2. table1 is the logtime table of employees and table2 is the groupcode of the employee. On table1 some employees has duplicate time in because they time in multiple time to just to secure their time in.

Table1

ID  EMPID       Time_IN
1   001     7:01 AM
2   004     7:04 AM
3   034     7:10 AM
4   034     7:11 AM
5   019     7:11 AM
6   019     7:12 AM

Table2

ID  empID   GroupName
1   001     AA
2   004     AB
3   034     AA
4   019     AA

result

GroupName   CNT
AA          5
AB          1

Expected result

GroupName   CNT
AA          3
AB          1

current query

Select b.GroupName, count(*) as cnt
from table1 a
inner join table2 b
on a.EMPID = b.empID    
Group by  b.GroupName

How can i achive as expected result above?

Thankyou in advance.

Answer

you can use distinct count as follows:

select t2.groupname, count(distinct empid) as cnt
from table1 t1 join table2 t2
on t1.empid = t2.empid
group by t2.groupname

Advertisement

Answer