sub subs, counts, with division problem

[capnwayne]

Hello all, first post and really new to sub subs too! If any has time, I'm trying to retrieve data like this:
Reason Countee Perc
BENT HEAD 26 (% OF 26 TO 92)
OTHER 24 (% OF 24 TO 92)
etc...

I'm using the SQL below but can't seem to work out the percentage calculation. (The below only returns 0's). Thanks for any help up front!!!

SELECT Reason, COUNT(Reason) AS Countee, COUNT(Reason) /
(SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK)AS Perc
FROM rb_power.RBCOBBLETRK
GROUP BY Reason
ORDER BY Countee DESC

Thanks!

[skhanal]

The number is getting rounded, multiply the numerator by 1.0 and you will be good.

SELECT Reason, COUNT(Reason) AS Countee, (COUNT(Reason) *1.0)/
(SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK)AS Perc
FROM rb_power.RBCOBBLETRK
GROUP BY Reason
ORDER BY Countee DESC

[capnwayne]

Wow! That was quick and it worked great!! Just as an add on - would it be difficult to format like 28.3%?

Thanks Again!

[skhanal]

You can get decimal (4,2) but if you need to get %, then you need to convert the numeric to character and concatenate '%'

SELECT Reason, COUNT(Reason) AS Countee, cast((COUNT(Reason) *1.0)/
(SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK) as decimal(4,2)) AS Perc
FROM rb_power.RBCOBBLETRK
GROUP BY Reason
ORDER BY Countee DESC

SELECT Reason, COUNT(Reason) AS Countee, cast(cast((COUNT(Reason) *1.0)/
(SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK) as decimal(4,2)) as char(5))+'%' AS Perc
FROM rb_power.RBCOBBLETRK
GROUP BY Reason
ORDER BY Countee DESC

[capnwayne]

thanks again! that's the ticket!

peace...
l8r