sql - 如何改进这个 Group by 子句

Question

我正在尝试使用以下 SQL 生成报告:

DECLARE @TOTALTRANS int
SET @TOTALTRANS = (SELECT count(*) FROM log_table where answear is not null) 

SELECT  
answear = ISNULL(answear,'Totalization') ,
COUNT(*) as Qtd,
CASE @TOTALTRANS
    WHEN 0 THEN '0%'
    ELSE CONCAT(CAST(((count(*) * 100.0 / @TOTALTRANS)) as decimal(5,2)), ' % ')
END as Porcent
FROM log_table 
WHERE answear is not null
GROUP BY answear WITH ROLLUP 

我得到了以下结果:

APPROVED 111111 1   0.58 % 
APPROVED 444444 164 95.91 % 
APPROVED 222222 1   0.58 % 
APPROVED 333333 1   0.58 % 
CANCELLED       4   2.34 % 
Totalization  171   100.00 % 

但我想得到这样的结果:

APPROVED        167 97.66 % 
CANCELLED         4  2.34 % 
Totalization    171 100.00 % 

该怎么做？甚至欢迎改进......我也在想也许可以使用 union 来做到这一点......

Answer 1

理想情况下，您希望规范化数据以拆分“已批准”和“111111”。如果做不到这一点，像这样的事情应该会让你接近:

DECLARE @TOTALTRANS int
SET @TOTALTRANS = (SELECT count(*) FROM log_table where answear is not null) 

;WITH CleanedUp AS (
  SELECT
    CASE 
      WHEN Answear LIKE 'APPROVED %' THEN 'APPROVED'
      ELSE Answear
    END as Answear
  FROM Log_Table
) 
SELECT
   answear = ISNULL(answear,'Totalization') ,
   COUNT(*) as Qtd,
   CASE @TOTALTRANS
      WHEN 0 THEN '0%'
      ELSE CONCAT(CAST(((count(*) * 100.0 / @TOTALTRANS)) as decimal(5,2)), ' % ')
END as Porcent
FROM CleanedUp
WHERE answear is not null
GROUP BY answear WITH ROLLUP