I am trying to make a report with the following 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
And I am having the following result:
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 %
But I'd like to get this result:
APPROVED 167 97.66 %
CANCELLED 4 2.34 %
Totalization 171 100.00 %
How to do that? Or even improvements are welcome....I am also thinking maybe do this using union...