Looping through a result set

[venerable bede]

SQL Server 2008
I need to loop through a result set n times and calculate percentages.

Lets say my result contains one field and looks like :

Field1
=====
2
2
1
0

Lets say that my parameter "n" = 3.
I must loop through the result set and calculate what the percentages are for the numbers 1 through 3 and return these.
In the above example I would get
One = 25%, Two = 50%, 3= 0%, Unknown = 25%.

I have been looking at this all day and for the life of me cannot figure out how to achieve it .

Any help would be much welcomed.

[jmcilhinney]

Are you looking at doing this in SQL code?

[venerable bede]

Yes.
I'm hoping to create a stored procedure which will take the parameter n. I'm looking at using a cursor at the moment but struggling a bit.

[FunkyDexter]

I'm typing this straight into the post without sytax checking so double check it but I think you want this:-

Select Field1, 100 * count(*)/(select count(*) From MyTable where Field1 <= @n)
From MyTable
Where Field1 <= @n
group by Field1

edit> how ironic is it that I miss-typed "syntax"?

[venerable bede]

Nice but that only works for each instance of n existing.
In the example i gave the result set would be :

Field1 Percentage
===============
0 25%
1 25%
2 50%

There needs to be a return vale for 3 as well. It needs to return something like :

Field1 Percentage
===============
0 25%
1 25%
2 50%
3 0%

Im distraught. Think Im going to cry

[CVMichael]

I'm not sure I fully understood your problem, (maybe if you give more examples).

"MyTable" is the table with your data

DECLARE @n INT

SET @n = 3

; WITH MyTable AS (
	SELECT 2 AS Field1 UNION ALL
	SELECT 2 UNION ALL
	SELECT 1 UNION ALL
	SELECT 0
)
, aa AS (
	SELECT Field1, CAST(Count(*) AS FLOAT) AS TCount
	FROM MyTable
	GROUP BY Field1
	
	UNION ALL
	
	SELECT @n, 0
)
, bb AS (
	SELECT CAST(SUM(TCount) AS FLOAT) AS STCount FROM aa
)
SELECT aa.Field1, CAST(CAST(aa.TCount / bb.STCount * 100.0 AS DECIMAL(4, 2)) AS VARCHAR(10)) + ' %' AS Percentage
FROM aa
	CROSS JOIN bb

It returns:

Field1	Percentage
0	25.00 %
1	25.00 %
2	50.00 %
3	0.00 %

[venerable bede]

Sorry CVMichael but I am not to sure about your syntax as I'm not really a db man.

I'm not to sure about the :

 WITH MyTable AS (
	SELECT 2 AS Field1 UNION ALL
	SELECT 2 UNION ALL
	SELECT 1 UNION ALL
	SELECT 0

P.S
I used to live in Oakville many years ago
I hope Canada is still as great a place as I remember.

[CVMichael]

Well, what part exactly ?

The "WITH" or the "UNION ALL" ?

I suggest to google both:
http://www.google.ca/#q=sql+server+with+statement
And also: http://www.vbforums.com/showthread.php?t=670621
http://www.google.ca/#q=sql+server+union+all

[FunkyDexter]

There needs to be a return vale for 3 as well

Ah, that's more problematic.

What you need to do (and what CV Michael is demonstrating) is join to a table containing all the possible numbers you might want output. I'm guessing no such table exists in your database so you're going to have to construct it in some way. CV is suggesting you do this using a Common Table Expression (that's what his WITH statement is about) which is a good suggestion. I personally prefer sub-queries because I find them more readable but it's really just a personal preference - go with whichever makes you more comfortable (there are some performance considerations but they're unlikely to affect you in this case)

A query like this will give you the table:-
Select 0 Union All
Select 1 Union All
Select 2 Union All
Select 3 Union All
... All the way up to the highest number you'll ever want.

It would be better if you could create that as a proper table in the database but a sub query or common table expression should do for now.

You can then use that query to left join to MyTable to get your percentage values. You'll end up with something like (again, not syntax checked):-

Select Numbers.Number, 
       cast(count(MyTable.Field1) as float)/(select cast(count(*) as float) From MyTable where Field1 <= @n)
From  (Select 0 as Number Union ALL
        Select 1 Union ALL
        Select 2 Union ALL
        Select 3) Numbers
Left Join MyTable
       on Numbers.Number = MyTable.Field1
Where Numbers.Number <= @n
Group By Numbers.Number

edit> corrected a couple of problems in my query