April 10, 2018 at 12:52 pm
Ran into some difficulties as follows
--How Many Students Stop at What Ranks
Select approxrank as 'Rank' , Count (*) as '# Of Students Who Stopped At This Rank'
--, , sum (CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), '# Of Students Who Stopped At This Rank')) = 1 then CONVERT(VARCHAR(12),'# Of Students Who Stopped At This Rank') else 0 End) As 'Percentage'
From PersonalFinance As PP
Inner join personalprofiles As PF
on PP.id = PF.id
Group By Approxrank
Order by '# Of Students Who Stopped At This Rank' desc
This yields a result set comprising the following two columns (sorry but don't know how to make the result set appear tabular);
Rank, # Of Students Who Stopped At This Rank
White 408
Orange 93
Yellow/Red Stripe 84
Yellow/White Stripe 81
NULL 77
I want a 3rd column derived from a calculation that divides each row in the ' # Of Students Who Stopped At This Rank' column by the sum of the column, then multiply by 100 to yield a %. So you'd have:
White 408 54.9%
Orange 93 12.5%
etc....
As you see above I've struggled as the Approxrank column is NVarchar and I hit a bunch of conversion errors. Short of altering the datatype (which I can but would rather not), is there a workaround?
TIA 🙂
April 11, 2018 at 5:07 am
Looks like the problem is the CONVERT inside the ISNUMERIC command. It has to resolve that CONVERT in order to determine whether or not things are a number or not. You need to have a mechanism to check the value first, then do any string concatenation. Instead, don't format stuff in T-SQL. It's not terribly conducive. This is the sort of thing that is better served in an app or reporting tool.
Boy, talk about making sure you set appropriate data types and don't overload column definitions. This is a perfect case. Yeah, change the data type to number if it's a number and string, if it's a string, and keep the two separated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2018 at 2:30 pm
I can't see any reason to test a COUNT(*) for "ISNUMERIC", since by definition a COUNT will be an int value.
Given that, I think this query does what you need, but of course I had no usable sample data from you to test it with:
--How Many Students Stop at What Ranks
Select Rank, [# Of Students Who Stopped At This Rank],
CAST([# Of Students Who Stopped At This Rank] * 100.0 /
SUM([# Of Students Who Stopped At This Rank]) OVER() AS decimal(4, 1)) AS Percentage
From (
Select approxrank as 'Rank' , Count (*) as '# Of Students Who Stopped At This Rank'
From dbo.PersonalFinance As PP
Inner join dbo.personalprofiles As PF
on PP.id = PF.id
Group By Approxrank
) as qry1
Order by [# Of Students Who Stopped At This Rank] Desc
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 13, 2018 at 6:04 am
ScottPletcher - Wednesday, April 11, 2018 2:30 PMI can't see any reason to test a COUNT(*) for "ISNUMERIC", since by definition a COUNT will be an int value.Given that, I think this query does what you need, but of course I had no usable sample data from you to test it with:
--How Many Students Stop at What Ranks
Select Rank, [# Of Students Who Stopped At This Rank],
CAST([# Of Students Who Stopped At This Rank] * 100.0 /
SUM([# Of Students Who Stopped At This Rank]) OVER() AS decimal(4, 1)) AS Percentage
From (
Select approxrank as 'Rank' , Count (*) as '# Of Students Who Stopped At This Rank'
From dbo.PersonalFinance As PP
Inner join dbo.personalprofiles As PF
on PP.id = PF.id
Group By Approxrank
) as qry1
Order by [# Of Students Who Stopped At This Rank] Desc
Scott, many thanks to you sir, this yields precisely what I was after. I think that's the difference between Devs and us DBA's who turn our hands to scripting, the use of the Rank function, along with a subquery was elegant.
Ironic that I was looking for a function whose name featured was already littered throughout my existing script 😉
Cheers again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply