record count

1. Senior Member
Join Date
Dec 1969
Posts
874

## record count

Hi I have the following db structure;<BR><BR>TABLE: test<BR>colA = Plan<BR>colB = mark<BR><BR>colA can have 5 different plans (eg. sports, news, dance, globe, circle etc)<BR>colB = is simply "yes" or "no"<BR><BR>colA colB<BR>sports yes<BR>news yes<BR>dance no<BR>sports yes<BR>globe yes<BR>sports no<BR>globe no<BR>etc<BR><BR>Problem: I want to get the %(percentage) of "yes" for each plan<BR>from the example, above sports plan would be: <BR>sports=66%<BR>news = 100%<BR>etc<BR><BR>Thanks.

2. Senior Member
Join Date
Dec 1969
Posts
2,050

## off the top of my head

let me know if this works for you?<BR><BR>count the number of each specific item with "yes", and divide by the number of total items. then multiply by 100.<BR><BR>sql = "SELECT Count(colA) AS theCount FROM Table WHERE colA = &#039;sports&#039; AND colB = &#039;yes&#039;"<BR>Set rs = conn.execute(sql)<BR><BR>sql = "SELECT Count(colA) AS theMax FROM Table WHERE colA = &#039;sports&#039;"<BR>Set maxRS = conn.execute(sql)<BR><BR>percentage = rs("theCount") / (maxRS("theMax") * 100)<BR><BR><BR>~~Chaotix (again, off the top of my head. could not work. let me know)

3. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: record count

select columnA Avg(columnB) from &#060;table&#062; group by columnA

4. Senior Member
Join Date
Dec 1969
Posts
2,050

## MUCH better solution.

ignore mine :)

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## Rforgot the where

where columnb = &#039;yes&#039;

6. Senior Member
Join Date
Dec 1969
Posts
874

## RE: Rforgot the where

hi thanks for the help;<BR><BR>I&#039;ve got:<BR>&#060;% <BR>Set rs = Server.CreateObject("ADODB.RecordSet") <BR>rs.open "select plan, Avg(correct) from mark where correct=&#039;yes&#039; group by plan", conn, adOpenStatic<BR>%&#062;<BR><BR>but how do I display<BR>PlanA = 34%<BR>PlanB=100%<BR>PlanC=10% ???<BR><BR>When I do this: <BR>&#060;% do while not rs.EOF %&#062;<BR>Plan:&#060;%=rs("plan")%&#062;&#060;%=r s("correct")%&#062;<BR><BR>&#060;% <BR> rs.MoveNext<BR> Loop<BR>%&#062; <BR>rs("correct") isn&#039;t found in the collection

7. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Rforgot the where

select plan, Avg(correct)as percen from mark where correct=&#039;yes&#039; group by plan<BR><BR>Note not percent percen (percent i believe is a reserved word)<BR><BR>&#060;%=formatpercent(rs("percen"))%& #062;<BR><BR>see http://www.devguru.com/Technologies/vbscript/quickref/formatpercent.html<BR>for more info<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
874

## RE: Rforgot the where

can you suggest why I keep getting %0.00 ?<BR>eg. Sports: % 0.00<BR> News: %0.00<BR><BR>regardless of how i format the formatpercent function.<BR><BR>&#060;% do while not rs.eof %&#062;<BR>&#060;%=rs("plan")%&#062;: &#060;%=formatpercent(rs("percen"))%&#062;<BR>< BR> &#060;% rs.moveNext<BR> loop<BR>%&#062;

9. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: Rforgot the where

I don&#039;t see what the average value of char or boolean column will be.<BR><BR>Which DBMS is this?<BR><BR>Access:<BR><BR>select plan,100.0*sum(iif(mark=&#039;Yes&#039;,1.0,0.0))/count(*) <BR>as percentage from t<BR>group by plan

10. Senior Member
Join Date
Dec 1969
Posts
11,247

## AS I said it's been a while (eop)

.

#### Posting Permissions

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