Getting % - SQL Server Forums

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

Getting %

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 10:59:42

Could you help me get the percentage of pending and cleared records from the below table and add it to this query?

SELECT reg, regname,
sum(MMPending) as MMPend,
sum(RecPending) as RecPend,
sum(MMPendingCleared) as MMCleared,
sum(RecPendingCleared) as RecCleared

FROM Totals
group by reg, regname
order by reg


reg    regname  MMPend  RecPend  MMCleared  RecClr
A	BOS	9729	6198	   166	      85
B	NYC	20889	12776	   318	     174
C	PHI	32808	19969	   533	     288
D	ATL	53399	33544	   713	     436
E	CHI	48222	29003	   651	     340
G	KCM	9593	6094	    87	      65
H	DAL	41955	25629	   548	     312
I	DEN	4081	2630	    72	      36
J	SFO	20972	12949	   312	     186
K	SEA	5635	3785	    88	      56

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-24 : 11:17:47

try this:


select
tt.reg
,tt.regname
,cast((tt.MMCleared*1.0/tt.MMPend)*100 as decimal(7,2)) as PendingPercent
,cast((tt.RecCleared*1.0/tt.RecPend)*100 as decimal(7,2)) as ClearedPercent
from
(
SELECT reg, regname,
sum(MMPending) as MMPend,
sum(RecPending) as RecPend,
sum(MMPendingCleared) as MMCleared,
sum(RecPendingCleared) as RecCleared

FROM Totals
group by reg, regname
--order by reg
)as tt
order by tt.reg

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 11:23:50

Thanks that worked but It's only displaying four columns:
Reg
Regname
PendingPercent
ClearedPercent

How do I get it to display:
MMPending,
RecPending,
MMPendingCleared
RecPendingCleared

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 11:28:20

Never mind I got it. I added them to the select statement. Thanks so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources