CODEWITHSUNDEEP
sql-server
Amol Naik
Amol Naik

Reputation: 453

Get count with percentage in SQL Server 2008

I am calculating count in SQL Server but with that count I also want percentage in another column with percentage(%) mark so please help

I want this output:

Total-  Resolved-   Repair -    Follow-Up-  Other
135     20          15          100         0
100%    14.81%      11.11%      74.07%      0%

My query to get count is this:

SELECT
    COUNT(*) as Total,
    (SELECT COUNT(*) FROM LMR WHERE StatusID=1) as 'Resolved',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=2) as 'Repair',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=3) as 'Follow-Up',
    (SELECT COUNT(*) FROM LMR WHERE StatusID=4) as 'Other'
FROM LMR;

Upvotes: 1

Views: 144

Answers (2)

Guillermo Zooby
Guillermo Zooby

Reputation: 612

Using Union all and converting your values to varchar maybe:

SELECT CONVERT(varchar(5),COUNT(*)) as Total,
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=1)) as 'Resolved',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=2)) as 'Repair',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=3)) as 'Follow-Up',
       CONVERT(varchar(5),(SELECT COUNT(*) FROM LMR WHERE StatusID=4)) as 'Other'
FROM LMR
UNION ALL 
SELECT CONVERT(varchar(5),COUNT(*)*100/COUNT(*))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=1)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=2)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=3)*100/COUNT(*)))+'%',
       CONVERT(varchar(5), ((SELECT COUNT(*) FROM LMR WHERE StatusID=4)*100/COUNT(*)))+'%'
FROM LMR

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

Use Conditional count instead of doing separate select for each status.

To calculate percentage (invidual_count/total_count) * 100

Try this

SELECT
    COUNT(*) as Total,
    COUNT(case when StatusID=1 then 1 end)  as 'Resolved',
    COUNT(case when StatusID=2 then 1 end) as 'Repair'
    COUNT(case when StatusID=3 then 1 end) as 'Follow-Up'
    COUNT(case when StatusID=4 then 1 end) as 'Other',
FROM LMR;
UNION ALL 
SELECT
    (COUNT(*)/COUNT(*))*100 as Total,
    (COUNT(case when StatusID=1 then 1 end)/NULLIF(COUNT(*),0))*100  as 'Resolved',
    (COUNT(case when StatusID=2 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Repair'
    (COUNT(case when StatusID=3 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Follow-Up'
    (COUNT(case when StatusID=4 then 1 end)/NULLIF(COUNT(*),0))*100 as 'Other',
FROM LMR;

Upvotes: 0

Related Questions