February 10, 2014 at 12:29 pm
Hi I am trying to think of the best way to calculate a count of 'no' values vs the count of total values. Also, if there aren't any 'no' values I need to gererate a 0%
this is what I have so far:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#temp_percent') IS NOT NULL
DROP TABLE #temp_percent
--===== Create the test table with
CREATE TABLE #temp_percent
(
value VARCHAR(50)
)
INSERT INTO #temp_percent
--(STATUSTRACKING)
SELECT 'no' UNION ALL
SELECT 'no' UNION ALL
SELECT 'no' UNION ALL
SELECT 'no' UNION ALL
SELECT 'no' UNION ALL
SELECT 'no' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes' UNION ALL
SELECT 'yes'
select
value, (Count(value)* 100 / (Select Count(*) From #temp_percent)) as Score
from #temp_percent
group by value
February 10, 2014 at 12:37 pm
Maybe something like this could help.
SELECT (COUNT( NULLIF(value, 'yes')) --eliminates yes values
/ (COUNT(*) * 1.0)) * 100 AS no_percentage ,
(COUNT( NULLIF(value, 'no')) --eliminates no values
/ (COUNT(*) * 1.0)) * 100 AS yes_percentage,
COUNT(*)
FROM #temp_percent
February 10, 2014 at 1:24 pm
Hi Luis, that works, thank you,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply