CODEWITHSUNDEEP
t-sqlgroup-by
Charles Okwuagwu
Charles Okwuagwu

Reputation: 10866

Add a percentage column to a simple t-SQL group by query

I have this simple query

select status,count(*) as [Count] from sms_deliverylog
where dt_log>'2015-03-23' and dt_log <'2015-03-24'
group by status with rollup

giving this result

status  Count
ACCEPTD 33
DELIVRD 554
EXPIRED 2
PENDING 72
REJECTD 1
UNDELIV 2
NULL    664

Thanks

EDIT: Please is there a simple t-sql alternative that does not involve using a CTE

Upvotes: 0

Views: 62

Answers (1)

Luke Ford
Luke Ford

Reputation: 144

Wouldn't call it simple, but here's my attempt:

SELECT
  status,
  count,
  ((CONVERT(DECIMAL, count)/(SELECT COUNT(*) FROM sms_deliverylog WHERE dt_log>'2015-03-23' and dt_log <'2015-03-24'))) * 100 AS '%'
FROM
(
  SELECT
    CASE 
      WHEN status IS NULL THEN 'Total' 
      ELSE status 
    END AS [status],
    COUNT(*) AS [count]
  FROM 
    sms_deliverylog
  WHERE 
    dt_log>'2015-03-23' and dt_log <'2015-03-24'
  GROUP BY STATUS WITH ROLLUP
 ) AS statuscounts

Fiddler Link: http://sqlfiddle.com/#!6/5549f/8/0

Upvotes: 1

Related Questions