sub subs, counts, with division problem

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

SQL Server 2000 Forums

Transact-SQL (2000)

sub subs, counts, with division problem

Author

Topic

capn_wayne
Starting Member

9 Posts

Posted - 2005-09-22 : 10:36:42

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 Again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 10:42:04

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

capn_wayne
Starting Member

9 Posts

Posted - 2005-09-22 : 10:48:01

The resulting data would look like this:

Reason Countee Perc
BENT HEAD 26 28.3%
OTHER 24 26.1%
ROLL OVER 11 12.0%
SPLIT 8 8.7%
OP. ERROR 6 6.5%
AUTOMATIC 5 5.4%
PC IN LINE 3 3.3%
SOFTWARE 3 3.3%
MECHANICAL 2 2.2%
NO BITE 2 2.2%
MAINT. ERROR 1 1.1%
SCANNER/HMD 1 1.1%

(sorry for spacing - not sure of proper coding for alignment with spacing)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 10:49:44

You posted only the result you want
Post sample data of your table
Refer this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

capn_wayne
Starting Member

9 Posts

Posted - 2005-09-22 : 10:58:26

Since I'm really counting on one column, that column example data is as follows:

Reason
SOFTWARE
OTHER
OP. ERROR
BENT HEAD
BENT HEAD
BENT HEAD
OTHER
MECHANICAL
BENT HEAD
OTHER
AUTOMATIC
OTHER
OTHER
BENT HEAD

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 11:06:14

Try this

Select Reason,count(Reason) as Countee, count(name)*100.0/(Select count(name) as Total from @t) as perc from yourTable group by Reason

Madhivanan

Failing to plan is Planning to fail

capn_wayne
Starting Member

9 Posts

Posted - 2005-09-22 : 11:11:11

That's it! Thanks a lot!! Any suggestions for reading material that might help me get more familiar with sub subs and the logic behind them?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-22 : 12:24:03

what is a sub sub ?

capn_wayne
Starting Member

9 Posts

Posted - 2005-09-22 : 13:42:17

Just meant Sub or nested SELECT statements.

l8r