Reputation: 1808
I have table SR with some records with statuses (more then 5 records this is just example ):
ID STATUS
1 NEW
2 OPEN
3 OPEN
4 PENDING
5 PENDING
I want to get table in this format:
STATUS PERCENTAGE
NEW 20
OPEN 40
PENDING 40
I wrote SQL query but I can't make it work:
with HELPTABLE as
(select count(*) as NUMBER
from SR
)
select
SR.STATUS,
(count (*)) / (HELPTABLE.NUMBER) * 100
from
SR, HELPTABLE
group by
SR.status
Using HELPTABLE I am getting as result 5.
If I don't put HELPTABLE.NUMBER
I am getting some result.
If I divide (count (*)
) with let's say with 2 I am getting result, but if I put HELPTABLE.NUMBER
which is 5 I am getting an error.
Thanks for help.
Upvotes: 1
Views: 309
Reputation: 432501
I'm guessing SQL Server 2005 or higher
select
T.STATUS, 100.0 * COUNT(*) / Total
from
(
SELECT
ID, STATUS, COUNT(*) OVER () AS Total
FROM
SR
) T
group by
T.status, T.Total
Upvotes: 5