November 26, 2007 at 2:28 pm
Hello,
Say I have a table with content as below:
ID Rule Pass Fail
1 rule1 0.3 0.7
2 rule2 0.3 0.7
3 rule3 0.1 0.9
4 rule4 0.5 0.5
5 rule5 0.4 0.6
6 rule6 0.7 0.3
....
another table
ID Rule Category
1 rule1 category1
2 rule2 category2
3 rule3 category3
4 rule4 category1
5 rule5 category2
6 rule6 category3
....
Now I want to do some calculation based on these two tables:
Get the average percentage by category
How do I do this? is there any quick way or I have to write a cursor to go through table1?
Thanks.
November 26, 2007 at 8:39 pm
I don't understand what value you want to get. Can you show how to get the value by hand? I don't understand the category part.
November 27, 2007 at 8:03 am
Hi, Thanks for reply, let me explain it again:
From the table1 you can see there are two fields(Pass/Fail) for each Rule, each rule corresponds to a category which is shown in table2.
What I need to do is to calculate the average based on category in table2, for example, I need to know what's the average Pass and Fail of category1. To get the answer, I need to get all the rules belong to category1, which are rule1 and rule4, then in table1, I add up Pass/Fail for rule1/4 and get the average value.
table1
ID Rule Pass Fail
1 rule1 0.3 0.7
2 rule2 0.3 0.7
3 rule3 0.1 0.9
4 rule4 0.5 0.5
5 rule5 0.4 0.6
6 rule6 0.7 0.3
....
table2
ID Rule Category
1 rule1 category1
2 rule2 category2
3 rule3 category3
4 rule4 category1
5 rule5 category2
6 rule6 category3
November 27, 2007 at 8:36 am
Something like
CREATE TABLE #Rule
(ID INT IDENTITY,
[Rule] CHAR(10),
Pass DECIMAL(5,4),
Fail DECIMAL (5,4))
INSERT INTO #Rule SELECT 'rule1', '0.3', '0.7'
INSERT INTO #Rule SELECT 'rule2', '0.3', '0.7'
INSERT INTO #Rule SELECT 'rule3', '0.1', '0.9'
INSERT INTO #Rule SELECT 'rule4', '0.5', '0.5'
INSERT INTO #Rule SELECT 'rule5', '0.4', '0.6'
INSERT INTO #Rule SELECT 'rule6', '0.7', '0.3'
CREATE TABLE #Category
(ID INT IDENTITY,
[Rule] CHAR(10),
Category CHAR(10))
INSERT INTO #Category SELECT 'rule1', 'category1'
INSERT INTO #Category SELECT 'rule2', 'category2'
INSERT INTO #Category SELECT 'rule3', 'category3'
INSERT INTO #Category SELECT 'rule4', 'category1'
INSERT INTO #Category SELECT 'rule5', 'category2'
INSERT INTO #Category SELECT 'rule6', 'category3'
SELECT
c.Category,
AVG(r.Pass),
AVG(r.Fail)
FROM
#Rule r INNER JOIN #Category c
ON r.[Rule] = c.[Rule]
GROUP BY
c.Category
November 27, 2007 at 9:18 am
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply