Reputation: 33
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...
Upvotes: 3
Views: 58
Reputation: 1271241
It looks like you just want anything up to the first space for the GROUP BY
:
SELECT (CASE WHEN answear IS NULL THEN 'Totalization'
ELSE LEFT(answear, CHARINDEX(' ', answear) - 1)
END) as Answear,
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 LEFT(answear, CHARINDEX(' ', answear) - 1) WITH ROLLUP ;
This version assumes that there is a space in answear
.
Upvotes: 0
Reputation: 85685
Ideally, you'd want to normalize your data to split out "Approved" and "111111". Failing that, something like this should get you close:
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
Upvotes: 1