show by percentage (revisit)

# Thread: show by percentage (revisit)

1. Member
Join Date
Dec 1969
Posts
32

## show by percentage (revisit)

Here was my question <BR><BR>I have&#039; got data A,B,C,D,E in a column called type. <BR>How could i calculate how many A(s) and B(s), and C(s) etc...in that column? <BR><BR><BR><BR>SELECT Letter, COUNT(Letter) FROM MyTable GROUP BY Letter (ReaperMan replied and still haven&#039;t said thanks)<BR><BR>I tried and it works. But becuase of my silliness , it only shows A,B,C and D. I loop like below<BR><BR>&#060;% Do While not rsMesstype.EOF<BR>Response.Write rstype("type")<BR>rstype.MoveNext <BR>Loop<BR>%&#062;<BR>How should it loop to make each data appear seperately and showing by percentage.<BR><BR>Thanks in advance.<BR>ttun

2. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: show by percentage (revisit)

&#060;table&#062;<BR>&#060;%<BR>Do While not rsMesstype.EOF<BR> Response.Write "&#060;tr&#062;"<BR> Response.Write "&#060;td&#062;" & rstype.Fields(0) & "&#060;/td&#062;<BR> Response.Write "&#060;td&#062;" & rstype.Fields(1) & "&#060;/td&#062;<BR> Response.Write "&#060;/tr&#062;"<BR> rstype.MoveNext <BR>Loop<BR>%&#062;<BR>&#060;/table&#062;<BR><BR>Is that what you&#039;re after? It won&#039;t show percentages - it&#039;ll show numbers.<BR><BR>If you want percentages, then you obviously need to know the total number of results before you write the information out (either by doing a different SQL query, or by modifying your current one, or by looping through the results first).<BR><BR>Craig.

3. Senior Member
Join Date
Dec 1969
Posts
16,931

## Actually...

&#060;table&#062; <BR>&#060;% <BR>Do While not rstype.EOF <BR> Response.Write "&#060;tr&#062;" <BR> Response.Write "&#060;td&#062;" & rstype.Fields(0) & "&#060;/td&#062; <BR> Response.Write "&#060;td&#062;" & rstype.Fields(1) & "&#060;/td&#062; <BR> Response.Write "&#060;/tr&#062;" <BR> rstype.MoveNext <BR>Loop <BR>%&#062; <BR>&#060;/table&#062;<BR><BR>You had two different recordset names...<BR><BR>Craig.

4. Member
Join Date
Dec 1969
Posts
32

## umm...still a bit of prob

hi Carig,<BR>First of all , i would like to say Thanks for getting back.<BR><BR>I tried with you sample...<BR>and i got the following error..<BR><BR>Error Type:<BR>Microsoft VBScript compilation (0x800A0409)<BR>Unterminated string constant<BR>/im283/percentage.asp, line 37, column 53<BR>Response.Write "&#060;td&#062;" & rsMesstype.Fields(A) & "&#060;/td&#062; ---&#062;line37<BR><BR>by the way, actually the name of the recordset is rsmesstype.<BR><BR>Here is the actual code...<BR>&#060;%<BR>strSQL= "SELECT Messagetype, COUNT(Messagetype) FROM Message GROUP BY Messagetype" <BR> Set rsMesstype = dcnDB.Execute(strSQL)<BR>%&#062;<BR><BR><BR>&#060; % Do While not rsMesstype.EOF<BR><BR> Response.Write "&#060;tr&#062;" <BR> Response.Write "&#060;td&#062;" & rsMesstype.Fields(A) & "&#060;/td&#062; <BR> Response.Write "&#060;td&#062;" & rsMesstype.Fields(B) & "&#060;/td&#062;<BR> Response.Write "&#060;td&#062;" & rsMesstype.Fields(C) & "&#060;/td&#062; <BR> Response.Write "&#060;td&#062;" & rsMesstype.Fields(D) & "&#060;/td&#062;<BR> Response.Write "&#060;td&#062;" & rsMesstype.Fields(E) & "&#060;/td&#062; <BR> Response.Write "&#060;/tr&#062;" <BR> rsMesstype.MoveNext <BR> Loop<BR> %&#062;<BR><BR>Thanks<BR>ttun

5. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: umm...still a bit of prob

Okay, my fault. There&#039;s a quote missing at hte end of that line.<BR>Response.Write "&#060;td&#062;" & rsMesstype.Fields(E) & "&#060;/td&#062;"<BR><BR>However, you&#039;re not going to be able to get your data out in that format, sorry. Each ROW in your recordset (each RECORD, if we&#039;re being pedantic) contains the letter, and the number of times that letter occurs.<BR><BR>What you&#039;re attempting to do in your code, is to pull back the counts for each letter in each single row. That&#039;s not going to be possible.<BR><BR>Not with your current query.<BR><BR>Try and get my code to work first. Mine&#039;ll make something like this:<BR>A 1200<BR>B 300<BR>C 2199<BR>D 1231<BR>F 0<BR><BR>It&#039;s not the same layout, but hopefully it&#039;ll show you some of the principles that are going on.<BR><BR>Craig.<BR>

6. Member
Join Date
Dec 1969
Posts
32

## Craig

Thanks Craig.<BR>It works as you mentioned.I will work out total and percentage later: after school.<BR><BR>Just out of personel curiousity, (i am just very keen in learning ASP)<BR><BR>can you please explain me the login behind<BR>rstype.Fields(0) and rstype.Fields(1) <BR><BR>I can&#039;t figure out.<BR>If i know , i will be able to use in related matter. (for instance, calculating total and percentage)<BR><BR>Thanks again<BR>cheers<BR>

7. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: Craig

Okay.<BR><BR>ADODB.Recordset.Fields is a collection of Field objects held in the recordset. If you want more information, MSDN holds a tonne of it (although it can be a nightmare to navigate - go have a search around).<BR><BR>As a collection, you can either reference a field called "Hello" as:<BR>rsType.Fields("Hello")<BR>Or, you can reference it using its ordinal reference:<BR>rsType.Fields(0)<BR><BR>The ordinal reference depends on the order in which it was SELECT-ed in your query:<BR>SELECT MyField1, MyField2, MyField3 FROM MyTable<BR>MyField1 has ordinal reference 0<BR>MyField2 has ordinal reference 1<BR>MyField3 has ordinal reference 2<BR><BR>It&#039;s simply a different method of accessing the same data.<BR><BR>As an aside, rsType.Fields(0) will give you the field OBJECT. It&#039;s a consequence of the lose-coding that VB/VBScript let you get away with that this works as you see it working above. The "default property" of the object is the ".Value" property. So these two lines are EXACTLY THE SAME:<BR>rsType.Fields(0)<BR>rsType.Fields(0).Valu e<BR><BR>But it&#039;s useful in other ways. Check out MSDN for a reference on the Field object - it has other properties that you can use:<BR>Response.Write "This field is called: " & rsType.Fields(0).Name<BR><BR>See...?<BR><BR>Craig.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•