CODEWITHSUNDEEP
sqlsql-server
Weslei Carraro
Weslei Carraro

Reputation: 33

How to improve this Group by clause

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

Mark Brackett
Mark Brackett

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

Related Questions