How to make a grouping?

Question

I have a table under

I want the result to be like

I run the following query

;WITH CTE AS ( Select script_Type = 'SP',detail_warnings ='Consider using EXISTS predicate instead of IN predicate' UNION ALL Select script_Type = 'SP',detail_warnings ='ExcludeItem does not exist in database SQLEye or is invalid for this operation' UNION ALL Select script_Type='SP',detail_warnings ='Values hardcoded in where-clause condition' UNION ALL Select script_Type='Table',detail_warnings ='Table name is not singular Remarks :1:- Missing create index statement.' UNION ALL Select script_Type='Table',detail_warnings ='Check for existence object then Drop statement before create statement' UNION ALL Select script_Type='View',detail_warnings ='Invalid name' ) SELECT script_Type,detail_warnings,COUNT(script_Type) FROM CTE c WITH(NOLOCK) GROUP BY ROLLUP(script_Type,detail_warnings) 

But the result is the same as with

What changes do I need to make to get the desired result?

Answer 1

You did all the hard work here, really, you just need to deal with the various ROLLUP lines in SELECT .

This looks pretty good to me:

 WITH CTE AS ( Select script_Type = 'SP',detail_warnings ='Consider using EXISTS predicate instead of IN predicate' UNION ALL Select script_Type = 'SP',detail_warnings ='ExcludeItem does not exist in database SQLEye or is invalid for this operation' UNION ALL Select script_Type='SP',detail_warnings ='Values hardcoded in where-clause condition' UNION ALL Select script_Type='Table',detail_warnings ='Table name is not singular Remarks :1:- Missing create index statement.' UNION ALL Select script_Type='Table',detail_warnings ='Check for existence object then Drop statement before create statement' UNION ALL Select script_Type='View',detail_warnings ='Invalid name' ) SELECT script_Type = case when script_Type is null and detail_warnings is null then 'Total' when detail_warnings is null then script_Type + ' Count' else script_Type end ,detail_warnings = isnull(detail_warnings, '') ,COUNT(script_Type) FROM CTE c WITH(NOLOCK) GROUP BY ROLLUP(script_Type,detail_warnings) 

SQL Fiddle with a demo .