Store procedure

1. Senior Member
Join Date
Dec 1969
Posts
141

## Store procedure

i am using store procedure...<BR><BR>if i have 3 field<BR><BR>ID, food, car<BR><BR>what i need to do is to find percentage on the field <BR>(food and car)<BR>and show no of records in this table<BR><BR><BR>for example<BR><BR>1&#124Benz&#124Apple<BR>2&#124Benz& #124Orange<BR>3&#124BMW&#124Pear<BR><BR>output <BR><BR>//show total of record is<BR><BR>RS("Total") =&#062; 3<BR><BR>//car (percetage to 2 decimal pl)<BR><BR>RS("car") = "Benz" =&#062; 66.67<BR>RS("car") = "BMW" =&#062; 33.33<BR><BR>food<BR><BR>RS("food") = "Apple" =&#062; 33.33<BR>RS("food") = "Orange" =&#062; 33.33<BR>RS("food") = "Pear" =&#062; 33.33<BR><BR>Thank you for your time

2. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Store procedure

I&#039;m not quite seeing what you&#039;re getting at. are you saying that the &#039;food&#039; and &#039;car&#039; columns have a load of duplicate information, and you want to extract some sort of percentage based on this?<BR><BR>j

3. Senior Member
Join Date
Dec 1969
Posts
5,955

## I think he does.

I think he does mean that. I&#039;m not sure what stored procedures have to do with it though.<BR><BR>You need to do an SQL query which groups the records, counts them and then calculates the percentage - unless you want to iterate through them in VBScript or JScript and do the calculations that way.<BR><BR>In SQL, you need three queries (unless you want to make one really complex query or something). Below is an example for the food:<BR><BR>Query [Count Food Total] -- this counts the number of entries in the table and will be the same for cars and fruit:<BR><BR>SELECT Count(Table1.food) AS CountOffood FROM Table1;<BR><BR>Query [Count Foods] -- this counts how often each fruit appears in the table:<BR><BR>SELECT Table1.food, Count(Table1.food) AS CountOffood1 FROM Table1 GROUP BY Table1.food;<BR><BR>Query [Food Percentage] -- this gives you the percentages:<BR><BR>SELECT [Count Foods].food, ([Count Foods].[CountOffood1]/[Count Food Total].[CountOffood])*100 AS Percentage FROM [Count Food Total], [Count Foods];<BR><BR>Hope this makes sense. I know it&#039;s quite messy.<BR><BR>Oliver.

#### Posting Permissions

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