There are a few ways you could do this. If the TEST table is not too big, something like this might work:SELECT TS_EXEC_STATUS as 'Status' , COUNT(*) as 'TOTAL' , 0 as SORT_ORDERFROM TESTWHERE TS_EXEC_STATUS <> ''GROUP BY TS_EXEC_STATUSUNION ALLSELECT 'Total' , COUNT(*) , 1FROM TESTWHERE TS_EXEC_STATUS <> ''UNION ALLSELECT 'Percent Passed' , CAST( (SELECT COUNT(*) from TEST where TS_EXEC_STATUS = 'Passed') as FLOAT) / (SELECT COUNT(*) from TEST where TS_EXEC_STATUS <> '') , 2 ORDER BY SORT_ORDER, TS_EXEC_STATUS
But I would suggest that you check out GROUP BY options, especially CUBE and ROLLUP. Also, have a look at windowing functions. For example, this query uses windowing to capture the count, grand total and ratio for each status in each row:SELECT TS_EXEC_STATUS as 'Status' , COUNT(*) over(partition by TS_EXEC_STATUS) as TOTAL , COUNT(*) over() as GRAND_TOTAL , CAST(COUNT(*) over(partition by TS_EXEC_STATUS) as FLOAT) / (COUNT(*) over ())FROM TESTWHERE TS_EXEC_STATUS <> ''GROUP BY TS_EXEC_STATUS
You could use that as a subquery then pick out the bits you want to report on. Even nicer if you use it as a CTE, something like this:;WITH StatusGroups as ( SELECT TS_EXEC_STATUS as 'Status' , COUNT(*) over(partition by TS_EXEC_STATUS) as TOTAL , COUNT(*) over() as GRAND_TOTAL , CAST(COUNT(*) over(partition by TS_EXEC_STATUS) as FLOAT) / (COUNT(*) over ()) AS Ratio FROM TEST WHERE TS_EXEC_STATUS <> '' GROUP BY TS_EXEC_STATUS )SELECT [Status], Total from StatusGroups UNION ALLSELECT TOP(1) 'Total', GRAND_TOTAL FROM StatusGroupsUNION ALLSELECT TOP(1) 'Percent Passed', Ratio*100.0 FROM StatusGroupsWHERE [Status] = 'Passed'