SQL query- number of statuses in percentage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query- number of statuses in percentage

Author

Topic

dejan88
Starting Member

7 Posts

Posted - 2012-03-07 : 06:52:17

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 TOTAL
from SR
)
select
SR.STATUS,
(count (*)) / (HELPTABLE.TOTAL) * 100
from
SR, HELPTABLE
group by
SR.status

Using just HELPTABLE I am getting as result 5.

If I don't put HELPTABLE.TOTAL in query I am getting some result.

If I divide (count (*)) with let's say with 2 I am getting result, but if I put HELPTABLE.TOTAL which is 5 I am getting an error.
I would really like to this query work with WITH statement

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-07 : 06:57:59

[code]
SELECT STATUS, COUNT(*) * 100.0 / COUNT(*) OVER()
FROM SR
GROUP BY STATUS
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dejan88
Starting Member

7 Posts

Posted - 2012-03-07 : 07:06:14

Thanks for answer but I am not getting correct results this is the results from original table.
28.57142857142857142
14.28571428571428571
2800.00000000000000000
14.28571428571428571
171.42857142857142857
14.28571428571428571
42.85714285714285714

And do u know maybe the solution with WITH statement? because I often used that command for such queries and I am wondering why I cant use it now?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-07 : 07:49:31

If you make this change to KH's query, it should work:

SELECT DISTINCT STATUS, COUNT(*) OVER (PARTITION BY STATUS) * 100.0 / COUNT(*) OVER()
FROM SR

If you do want to use a cte, you can do the following:

;WITH cte AS (SELECT COUNT(*) Total FROM #tmp)
SELECT
	STATUS,
	100.0*COUNT(*)/Total
FROM
	SR CROSS JOIN cte
GROUP BY
	STATUS,
	Total;

dejan88
Starting Member

7 Posts

Posted - 2012-03-07 : 08:12:42

Thanks sunitback you are genius:)
Thanks khtan for idea you helped me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-07 : 09:48:17

oops sorry, should be like this


SELECT	STATUS, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()
FROM	SR
GROUP BY STATUS

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources