CASE n RANK Function in T-SQL

CASE n RANK Function in T-SQL

--CASE

SELECT

     SUM(CASE WHEN Rate>0 AND Rate<10 THEN 1 ELSE NULL END) sal1,

SUM(CASE WHEN Rate>10 AND Rate<20 THEN 1 ELSE NULL END) sal2,

SUM(CASE WHEN Rate>20 AND Rate<100 THEN 1 ELSE NULL END) sal3,      

SUM(CASE WHEN Rate>100 AND Rate<10000 THEN 1 ELSE NULL END) sal4 

  FROM HumanResources.EmployeePayHistory

--RANK

SELECT  Rate,   

       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  

FROM HumanResources.EmployeePayHistory AS cmp ;