Dear All Any one can solve this issue. I have one table Name:calls_table ID Division Status 1001 A Open 1002 B Closed 1003 A Closed 1004 C Open 1005 C Open i need a query for number of call received based on each division and number of calls closed based on each division with percentage of this closed calls. Please kindly give me the query of this table. Thanks Sathik
Here you go: DECLARE @calls_table TABLE ( ID INT , Division CHAR(1) , [STATUS] VARCHAR(50) ) INSERT INTO @calls_table ( ID, Division, STATUS ) VALUES ( 1001, 'A', 'Open' ), ( 1002, 'B', 'Closed' ), ( 1003, 'A', 'Closed' ), ( 1004, 'C', 'Open' ), ( 1005, 'C', 'Open' ) SELECT Division , SUM(CASE WHEN STATUS = 'Open' THEN 1 ELSE 0 END) AS Open_Calls , SUM(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END) AS Closed_Calls , COUNT(*) AS 'Total_Calls' , CONVERT(DECIMAL(5, 2), SUM(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END)) / COUNT(*) AS Closed_Calls_Perc FROM @calls_table GROUP BY Division Hope that helps.