sql server - Counts the table values using inner join in SQL -

i have 2 tables:

1. application_info
2. application_movement

when user apply job, details inserted application_info table , hr activities hold candidate, decline, shortlist , schedule updated in application_movement table.

application_info:

applicationid    appliedjobid    emailid               status -------------------------------------------------------------------      1                3          usercandi@gmail.com   pending      2                9          user2@gmail.com       pending      3                2          user3@gmail.com       pending 

applicaiton_movement:

applicationid  fromstatus  tostatus         movementemailid -------------------------------------------------------- 1                pending      hold             hr@gmail.com 1                hold        **decline**       hr@gmail.com 2                pending      shortlist        hr1@gmail.com 2                shortlist   **scheduled**     hr1@gmail.com 3                pending     **scheduled**     hr@gmail.com 

hr hold, decline, shortlist , scheduled, need counts of hr@gmail.com , hr1@gmail.com (how many activities did counts hold, decline, shortlist , scheduled interview tostatus column)

in application_movement, applicationid 2 or more times.. need count of last updated tostatus count only..

  i.e   hr@gmail.com     decline(1)   scheduled(1)            hr1@gmail.com    scheduled(1)  

just need select query base on 2 tables of applicationid fetch counts.

select count(distinct(tostatus)))  ,tostatus         , movementemailid  tostatus in ('decline' , 'shortlist' , 'scheduled') group tostatus         , movementemailid 

hope suffice requirement, give output , can put order on tostatus or movementemailid per preference.

count            tostatus         movementemailid -------------------------------------------------------- 1                decline       hr@gmail.com 1                 shortlist        hr1@gmail.com 1                scheduled     hr1@gmail.com 1                scheduled     hr@gmail.com