How to do the Grouping?

Question

I have a table as under

I want the output to be as

I fire the below 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 output is as under

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

Answer 1

You've done all the hard work here, really, you just need to deal with the various ROLLUP rows in the 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 demo.