We help IT Professionals succeed at work.

# getting percentages and display issues

on
265 Views
Hi,

I have an online survey and the results are stored in one table. I need to display the results as percentages so I'm trying to figure out how to pull (in this example) the States (if they exist in the table) from the table with the count and then display them with a percentage. I need a count of the total states listed so that I can divide by that to get the percentage. I'm not sure how to do that, also, my display is way off. I wanted to make it look like this:

AL   AR       CA      IL       KY
2%   15%    5%     0%    9%

Whatever states are in the table, with the percentage below. I only wanted to display about 8 per row
and then go to another row to complete the next 8. But of course I'm doing it wrong, so I get this

'AK'
'2' 'AR'
'3' 'CA'
'2' 'IL'
'1' 'KY'
'1' 'none'
'3' 'OR'
'1' 'Other/Not Listed'
'1' 'TX'
'1' 'WY'

If anyone could help me with the percentage or the display I would really appreciate it. I'm attaching my code below:
``````Private Sub CountState()
Dim vHtml, q4, numrec
q4 = "4. State of Current Residence:"
Dim Myconn As SqlConnection
Dim Mycomm As SqlCommand
Dim sql As String
Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
Myconn.Open()
sql = "SELECT DISTINCT Count(state) AS StateCount, state FROM tbl_Alumni_Survey_data GROUP BY state"
vHtml = vHtml & "<table cellpadding=""3"" cellspacing=""1"" width=""100%"" align=""center"">"
vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
vHtml = vHtml & "<th colspan=""9"" valign=""bottom"" width=""25%"" align=""left"">"
vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
vHtml = vHtml & "</th>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""E5E5E5"">"
Mycomm = New SqlCommand(sql, Myconn)
If dtr.HasRows Then

vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1"">'" & dtr("state") & "'</FONT></TD>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""#B9C0D0"">"
vHtml = vHtml & "<th valign=""bottom"" width=""11%"" align=""left"">"
vHtml = vHtml & "<FONT face=""Verdana"" size=""1"" align=""center"">'" & dtr("StateCount") & "'</FONT>"
vHtml = vHtml & "</th>"
End While
End If
dtr.Close()
Myconn.Close()
lblResults.Text = vHtml
End Sub
``````
Comment
Watch Question

## View Solution Only

Commented:
If you want all the states on top and their percentages below you have to first loop over all dtr("state") and then insert a , then loop over all percentages.

To do this, since you are using an SQL reader, you will have to first read everything into a 2D array, and then generate the HTML in the required order.

To get the percentage simple just add a % sign after you've output the state count:
...align=""center"">'" & dtr("StateCount") & "%'"

Also, if you don't want the single quotes appearing, remove the single quotes right before you close and right after you open your double quotes before outputting a variable, although I'm sure you knew that.

Once all this is worked out, you can add a counter to your loop. Every time the counter MOD 8 equals 0, you can start a new row.

So it would look like this:

2. In a loop, save all states and statecounts in a 2D array
3. Initialize i = 0, j = 0
3. Start a loop that goes until all your entries have been output
4. Start a while loop, while i+1 MOD 8 != 0
5. Display state i
6. i ++
7. Start a new table row ()
8  Start a while loop, while j+1 MOD 8 != 0
9. Display statecount j
10. j++
11. Start a new table row
12. End the loop if you're done displaying everything

You can check if you've finished displaying everything with a boolean variable.
Also make sure that you're checking your index bounds in each of the internal loops. Neither i nor j should ever exceed the number of states you have. Once they do, set your boolean variable indicating that you are done to true, and exit the while loop.

Commented:
Wow, all of that sounds really helpful, but unfortunately it's over my head. I'm not even sure how to load a 2D array with the states and state counts. Do you have any examples of this? Also, about the percentage...(and maybe you are telling me how to do it and I am just not catching on) I have the counts of how many times each state is in the database, but I need a count of the total states in order to divide and get my percentage. Of course, you may have been telling me how to do that with the 2D array and everything else you told me. I'm sorry to say I'm just lost. I really do appreciate you help and time.

Commented:
Well you will have to calculate the percentages then. In the loop when you're adding all the values to an array, add the state count to a variable "TotalStateCount". Then when you go to display the percentages you will display cInt(StateCount(j)/TotalStateCount*100) & "%"

Your best bet if you're a beginner is to make two 1D arrays. Unfortunately for you, you don't know how many results you will get, so you'll have to use array lists.

I haven't tested the code below so it might need some massaging, but it's essentially what you need. You can add the formatting yourself after you check that it works.
``````Dim states as new ArrayList(), stateCounts as new ArrayList()
Dim totalCount = 0, tempCount as Integer
...

If dtr.HasRows Then
tempCount = cInt(dtr("StateCount")) 'Convert to an integer'
totalCount += tempCount 'Add count to the total'
End While

'Now we can display the statistics'
Dim done as boolean = false
Dim i = 0, j as Integer = 0
Do
While i+1 Mod 8 <> 0 And i < states.Count
vHtml &= "<TD> " & states(i) & " </TD>"
i += 1
Loop
vHtml &= "</TR><TR>"
While j+1 Mod 8 <> 0 And j < stateCounts.Count
vHtml &= "<TD> " & cInt(stateCounts(j) / totalCount * 100) & "% </TD>"
j += 1
Loop
vHtml &= "</TR><TR>"
If j = stateCounts.Count then done = True 'Check if we are done'
Loop While done = False
End If
``````

Commented:
Hi,

Thank you so much for the help!  It works in that it has the states on top and the percentage below, but it's not starting a new row after 8 states. I'll attach the code, maybe I did something to the code you gave me that caused that part not to work. Another thing I was wondering about, in the list there is "none" (which means they did not answer the question) and "Other/Not Listed". Is there a way that I can list everything in alphabetical order (the way it is now) except to have the "none" and "Other/Not Listed" appear at the end of the list? Again, thank you so much for your help.  I know I'm difficult to deal with since I'm not very familiar with a lot of this.

Commented:
Oh, sorry, I forgot to include the code:
``````Private Sub CountState2()
Dim vHtml, q4, numrec
q4 = "4. State of Current Residence:"
Dim Myconn As SqlConnection
Dim Mycomm As SqlCommand
Dim sql As String
Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
Myconn.Open()
sql = "SELECT DISTINCT Count(state) AS StateCount, state FROM tbl_Alumni_Survey_data GROUP BY state"
vHtml = vHtml & "<table cellpadding=""3"" cellspacing=""1"" width=""100%"" align=""center"">"
vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
vHtml = vHtml & "<th colspan=""10"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
vHtml = vHtml & "</th>"
vHtml = vHtml & "</tr>"
Mycomm = New SqlCommand(sql, Myconn)
Dim states As New ArrayList, stateCounts As New ArrayList
Dim totalCount = 0, tempCount As Integer
If dtr.HasRows Then
tempCount = CInt(dtr("StateCount")) 'Convert to an integer'
totalCount += tempCount 'Add count to the total'
End While

'Now we can display the statistics'
Dim done As Boolean = False
Dim i = 0, j As Integer = 0
Do
While i + 1 Mod 8 <> 0 And i < states.Count
'vHtml &= "<tr bgcolor=""E5E5E5"">"
vHtml &= "<TD align=""left"" bgcolor=""E5E5E5""><FONT face=""Verdana"" size=""1"">" & states(i) & " </FONT></TD>"
'vHtml &= "<TD> " & states(i) & " </TD>"
i += 1
End While
vHtml &= "</TR><TR>"
While j + 1 Mod 8 <> 0 And j < stateCounts.Count
vHtml &= "<TD align=""left"" bgcolor=""#B9C0D0""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
'vHtml &= "<TD> " & CInt(stateCounts(j) / totalCount * 100) & "% </TD>"
j += 1
End While
vHtml &= "</TR><TR>"
If j = stateCounts.Count Then done = True 'Check if we are done'
Loop While done = False
End If
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""#E5E5E5"">"
vHtml = vHtml & "<td colspan=""10"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "&nbsp;</td>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
vHtml = vHtml & "<td colspan=""10"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "&nbsp;</td>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "</table>"
lblResults.Text = vHtml
End Sub
``````

Commented:
Hey. I know why it's not working. Silly order of operations. Replace This:
While i + 1 Mod 8 <> 0 And i < states.Count
While j + 1 Mod 8 <> 0 And j < stateCounts.Count
With This:
While (i + 1) Mod 8 <> 0 And i < states.Count
While (j + 1) Mod 8 <> 0 And j < stateCounts.Count

That will put them in groups of 8.

Changing the sorted order isn't such an easy modification to make. If everything you initially asked for works, you should close the question, and open up a new question with the working code attached and ask the follow-up question about special case sorting.

Cheers :)

--
Alain

Commented:
Hi,

I apologize for taking so long to respond. When I add the parenthesis like you suggested it basically just stalls out. It's like it keeps trying to run, but the display never comes up. Again, I thank you for taking so much time with me. It's been a huge help. I'm including the code just so you can look it over and make sure I'm adding this in the right place.
`````` Dim q4
q4 = "4. State of Current Residence:"
Dim Myconn As SqlConnection
Dim Mycomm As SqlCommand
Dim sql As String
Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
Myconn.Open()
sql = "SELECT DISTINCT Count(state) AS StateCount, state FROM tbl_Alumni_Survey_data GROUP BY state"
vHtml = vHtml & "<table cellpadding=""3"" cellspacing=""1"" width=""100%"" align=""center"">"
vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
vHtml = vHtml & "<th colspan=""11"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
vHtml = vHtml & "</th>"
vHtml = vHtml & "</tr>"
vHtml &= "<tr bgcolor=""E5E5E5"">"
vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1""></FONT></TD>"
Mycomm = New SqlCommand(sql, Myconn)
Dim states As New ArrayList, stateCounts As New ArrayList
Dim totalCount = 0, tempCount As Integer
If dtr.HasRows Then
tempCount = CInt(dtr("StateCount")) 'Convert to an integer'
totalCount += tempCount 'Add count to the total'
End While

'display the statistics
Dim done As Boolean = False
Dim i = 0, j As Integer = 0
Do

While (i + 1) Mod 8 <> 0 And i < states.Count
'vHtml &= "<tr bgcolor=""E5E5E5"">"
If states(i) = "" Then
vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">Omit</FONT></>"
Else

vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">" & states(i) & " </FONT></TD>"
End If
i += 1
End While
vHtml &= "</TR><TR  bgcolor=""#B9C0D0"">"
vHtml = vHtml & "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1""></FONT></TD>"
While (j + 1) Mod 8 <> 0 And j < stateCounts.Count

vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
vHtml = vHtml & "</th>"
'vHtml &= "<TD> " & CInt(stateCounts(j) / totalCount * 100) & "% </TD>"
j += 1
End While
vHtml &= "</TR><TR>"
If j = stateCounts.Count Then done = True 'Check if we are done'
Loop While done = False
End If
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""#E5E5E5"">"
vHtml = vHtml & "<td colspan=""11"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "&nbsp;</td>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
vHtml = vHtml & "<td colspan=""11"" valign=""bottom"" align=""left"">"
vHtml = vHtml & "&nbsp;</td>"
vHtml = vHtml & "</tr>"
vHtml = vHtml & "</table>"
vHtml = vHtml & "<br/>"
lblResults.Text = vHtml
``````

Commented:
okay, replace the above code with this:
``````            'display the statistics'
Dim done As Boolean = False
Dim rowCount = 0, i = 0, j As Integer = 0
Do
While Int((i + 1) / 8) = rowCount And i < states.Count
If states(i) = "" Then
vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">Omit</FONT></>"
Else

vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">" & states(i) & " </FONT></TD>"
End If
i += 1
End While
vHtml &= "</TR><TR  bgcolor=""#B9C0D0"">"
vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1""></FONT></TD>"
While Int((j + 1) / 8) = rowCount And j < stateCounts.Count

vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
vHtml &= "</th>"
j += 1
End While
vHtml &= "</TR><TR>"
rRowCount = rowCount + 1
If j = stateCounts.Count Then done = True 'Check if we are done'
Loop While done = False
``````

Commented:
typo:

replace
rRowCount = rowCount + 1
with
rowCount += 1

Commented:
That works great, except for on the first row.....it only goes to 7 and then starts and new row. The the two rows after that go to 8.

Commented:
not true - 0 is a valid index. It should write
State0, State1, State2, State3, State4, State5, State6, State7, (8 states total)

is this not the case when you run it?

Commented:
If it's not appearing as it should I'm guessing the problem is that in your database one of your states being returned is an empty string ("")
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
AWESOME!!!!!! That works perfectly!!! I'm sorry I've been so much trouble, but I can't tell you how much I appreciate your help. I do have one more question. I have several questions that this code would work with on a page. Currently I'm trying to run it like this (code below). I was wondering if there is some way that I could set it up as a generic function and run different questions through it? Like some type of loop? Or do you think it will matter if I am doing it the way I currently am. There is a total of 13 questions on the screen and I could use the code for about 8 of them. I can open another question if you think it's possible. I'm just wondering what you think about it. Thanks so much for everything!!!!

Commented:
Go ahead an open another question (and this time remember to attach the code ;) ) and I'll see if I can help.

Commented:
lol, I'm sorry, I always forget the code. I just thought about that!

Commented:
Thanks so much for your help!

Commented:
Make sure you link to the new question!

Commented:
alainbryden,

I opened a new question called "can this code be ran in a loop as a generic function?" I appreciate you help so much. Thanks again!
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.