Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 $10 the first person to figure out this SQL Math Q

Author  Topic 

jaroot
Starting Member

46 Posts
Posted - 2005-06-02 : 18:16:55
The first person that is able to figure this out for me.. I will send $10 to you via check/mo/paypal/neteller, or I will take you out to lunch/dinner if you live in the vacinity - NO JOKE. I know it's not a lot.. but it's just a token of my appreciation for your help. I'm freakin stumped on this one as it goes way beyond my T-SQL abilities.

I have a recordset that looks like the following:
ID Score Per1 Per2 Category
101 1 22 11 21
101 1 58 11 21
101 1 20 11 21
101 2 12 89 22
101 2 12 89 22
101 2 76 89 22

104 4 17 10 74
104 1 25 10 74
104 2 25 10 74
104 3 33 10 74
104 5 33 90 75
104 5 33 90 75
104 5 34 90 75

First I need to do the following calculation in T-SQL somehow on each row... Which I can figure out.. but here is the formula:
Score * (Per1 * .01) * (Per2 * .01) <--- for each row

The part I can't figure out is how to take that score, sum up the total for EACH Category within an ID, then divide the two categories to get the average.

So for ID 101 for instance.. I need to do something like:
Category 21 =
1*22*.01*11*.01
+
1*58*.01*11*.01
+
1*20*.01*11*.01
Category 22 =
2*12*.01*89*.01
+
2*12*.01*89*.01
+
2*76*.01*89*.01

Then I need the avg of Cat21/22 ...
((Category 21 + Category 22) / 2)

Then repeat the same thing for the next ID 104, then simply total the averages. In the recordset there will be 1 to Infinate # of ID's...

In this case I just need:
((Category 21 + Category 22) / 2) + ((Category 74 + Category 75) / 2)

Again.. help I know there are better minds than mine out there who can write a query to do this. Or at least a cost efficient way to do this T-SQL.

Thanks!
-Jason

jaroot
Starting Member

46 Posts
Posted - 2005-06-02 : 18:26:23
The answers you should come up with are:
Category 21= 0.1100
Category 22= 1.7800
-AVG: 0.9450
Category 74= 0.2420
Category 75= 4.5000
-AVG: 2.3710

2.3710 + 0.9450 = 3.316 <-- this is the magic # i'm looking for (assuming I did the math right)
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 18:30:11
I'm pretty close on this one, but it's not a easy one!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

jaroot
Starting Member

46 Posts
Posted - 2005-06-02 : 18:32:04
Also... I cannot use a cursor to do this, so the results have to be returned within a recordset. (Or if you can figure out a way to do it w/out a cursor.. that'd be fine w/ me!)
Go to Top of Page

jaroot
Starting Member

46 Posts
Posted - 2005-06-02 : 18:33:52
quote:
Originally posted by MichaelP

I'm pretty close on this one, but it's not a easy one!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>


heh.. ya I know.. I've gotten close on it, but I just can't seem to figure out how to do the subsets of ID's and Categories then do the AVG's for each. My brain overloads at that point [CORE DUMP] :)
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-02 : 18:37:12 



Create Table #myTable (id int, score int, per1 int, per2 int, cat int)
Insert Into #myTable
Select 101, 1, 22, 11, 21 Union All
Select 101, 1, 58, 11, 21 Union All
Select 101, 1, 20, 11, 21 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 76, 89, 22 Union All

Select 104, 4, 17, 10, 74 Union All
Select 104, 1, 25, 10, 74 Union All
Select 104, 2, 25, 10, 74 Union All
Select 104, 3, 33, 10, 74 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 34, 90, 75

Select 
	id,
	cat_21_22 = sum(case when cat in (21,22) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2,
	cat_74_75 = sum(case when cat in (74,75) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2,
	ttl = sum(case when cat in (21,22) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2 + sum(case when cat in (74,75) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2
From #myTable
Where cat in (21,22,74,75)
Group By id

--EDIT: For the magic number...
Select 
	cat_21_22 = sum(case when cat in (21,22) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2,
	cat_74_75 = sum(case when cat in (74,75) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2,
	ttl = sum(case when cat in (21,22,74,75) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01) else 0 end)/2
From #myTable
Where cat in (21,22,74,75)


Drop Table #myTable


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 18:39:16
Corey, that doesn't handle N number of ID and N number of cats per ID though.....

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 18:40:40
If I could figure out how to count the number of distinct categories per ID, I'd have this thing. It should be simple, but I can't get it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-02 : 18:41:04
magic number matches though??... maybe the question could be clearer??

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-02 : 18:46:20
why do you need to count the distinct categories?? look at the math:

quote:

Category 21 =
1*22*.01*11*.01
+
1*58*.01*11*.01
+
1*20*.01*11*.01
Category 22 =
2*12*.01*89*.01
+
2*12*.01*89*.01
+
2*76*.01*89*.01

Then I need the avg of Cat21/22 ...
((Category 21 + Category 22) / 2)


each row calc is the same. lets call it f(rowNum)

he wants to average the total of cat1 and cat2
cat1 = f(cat1_1) + f(cat1_2) + ... + f(cat1_n)
cat2 = f(cat2_1) + f(cat2_2) + ... + f(cat2_n)

so (cat1 + cat2)/2 = f(cat1_1) + f(cat1_2) + ... + f(cat1_n) + f(cat2_1) + f(cat2_2) + ... + f(cat2_n)
or (f(1) + ... + f(n))/2

(cat21 + cat22)/2 + (cat74 + cat75)/2 = (cat21 + cat22 + cat74 + cat75)/2

so sum every row calc and then divide by 2.




Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 18:58:43
Corey,
If I read it right, the magic number is the sum of the average per ID.

So, you need to know how many categories per ID there are, then sum the category totals, then divde that number by the number of categories per ID, then sum the averages per ID.

It's hard putting that into words :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-02 : 19:03:26
fine.... does this pass your test 


Create Table #myTable (id int, score int, per1 int, per2 int, cat int)
Insert Into #myTable
Select 101, 1, 22, 11, 21 Union All
Select 101, 1, 58, 11, 21 Union All
Select 101, 1, 20, 11, 21 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 76, 89, 22 Union All

Select 104, 4, 17, 10, 74 Union All
Select 104, 1, 25, 10, 74 Union All
Select 104, 2, 25, 10, 74 Union All
Select 104, 3, 33, 10, 74 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 34, 90, 75


Select
	ttl = sum(ttl)
From
	(Select 
		ttl = case when cat in (21,22,74,75) then convert(float,Score)*(convert(float,per1)*.01)*(convert(float,per2)*.01)/(Select count(distinct cat) From #myTable Where id=A.id) else 0 end
	From #myTable A
	Where cat in (21,22,74,75)) Z

Drop Table #myTable


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 19:05:00
YES!! I got it!!

DECLARE @myTable TABLE(ID INT, Score INT, Per1 INT, Per2 INT, Category INT)

INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 1, 22, 11, 21)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 1, 58, 11, 21)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 1, 20, 11, 21)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 2, 12, 89, 22)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 2, 12, 89, 22)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(101, 2, 76, 89, 22)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 4, 17, 10, 74)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 1, 25, 10, 74)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 2, 25, 10, 74)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 3, 33, 10, 74)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 5, 33, 90, 75)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 5, 33, 90, 75)
INSERT INTO @mytable(ID, Score, Per1, Per2, Category) VALUES(104, 5, 34, 90, 75)

-- select ID, Category, (Score * (per1 * .01) * (Per2 * .01)) As TheForumlaThing
-- FROM @MyTable

/*
SELECT ID, Count(*) FROM (
SELECT ID, Category, COUNT(*) AS TheCount FROM @MyTable 
GROUP BY ID, category
) C 
GROUP BY ID
*/


SELECT ID, SUM(SumOfCategory) AS SumPerID
INTO #MyTempTable
FROM (
	SELECT ID, Category, SUM(TheForumlaThing) AS SumOfCategory
	FROM (
		select ID, Category, (Score * (per1 * .01) * (Per2 * .01)) As TheForumlaThing
		FROM @MyTable
	) A
	GROUP BY a.ID, a.Category
) B
GROUP BY b.ID


SELECT c.ID, Count(*) AS NumCatsPerID, (mtt.SumPerID / Count(*)) AS SumOfAverages FROM (
SELECT ID, Category, COUNT(*) AS TheCount FROM @MyTable 
GROUP BY ID, category
) C 
INNER JOIN #MyTempTable mtt ON mtt.ID = c.ID
GROUP BY c.ID, mtt.SumPerID

--The Final Answer!
SELECT SUM(SumOfAverages)
FROM (
	SELECT c.ID, Count(*) AS NumCatsPerID, (mtt.SumPerID / Count(*)) AS SumOfAverages FROM (
	SELECT ID, Category, COUNT(*) AS TheCount FROM @MyTable 
	GROUP BY ID, category
	) C 
	INNER JOIN #MyTempTable mtt ON mtt.ID = c.ID
	GROUP BY c.ID, mtt.SumPerID
) F

DROP TABLE #MyTempTable


Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-06-02 : 19:07:27
OK, jaroot, which solution is the right one?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-02 : 19:09:05
michael.. did you see my last one?? it works as you interpreted the problem...

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-06-03 : 04:21:22
Uh, what's going on ?
What's wrong with this ?
Why hardcoded values ?
Why so much code ?
What am I doing here ?

Create Table #myTable (id int, score int, per1 int, per2 int, cat int)
Insert Into #myTable
Select 101, 1, 22, 11, 21 Union All
Select 101, 1, 58, 11, 21 Union All
Select 101, 1, 20, 11, 21 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 12, 89, 22 Union All
Select 101, 2, 76, 89, 22 Union All

Select 104, 4, 17, 10, 74 Union All
Select 104, 1, 25, 10, 74 Union All
Select 104, 2, 25, 10, 74 Union All
Select 104, 3, 33, 10, 74 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 33, 90, 75 Union All
Select 104, 5, 34, 90, 75


SELECT SUM(AvgById) AS magic_number
FROM
	(
		SELECT id, AVG(SumByCat) AS AvgById
		FROM
			(
				SELECT id, cat, .0001*SUM(Score*Per1*Per2) AS SumByCat
				FROM #myTable
				GROUP BY id, cat
			) AS X
		GROUP BY id
	) AS Y


DROP TABLE #myTable


rockmoose
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-03 : 07:19:22
i guess all thats really needed is:


Select
	ttl = sum(ttl)
From
	(Select 
		ttl = (convert(float,Score*per1*per2)*.0001)/(Select count(distinct cat) From #myTable Where id=A.id)
	From #myTable A) Z

Drop Table #myTable


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-06-03 : 07:50:51
Yes, more compact !

rockmoose
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-03 : 08:11:18
I think we managed to exhaust this topic, so, jaroot, does that cover it for you!?!

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

jaroot
Starting Member

46 Posts
Posted - 2005-06-03 : 09:58:12
Heh.. yes indeed. Just read all the solutions.. damn you guys are smart! Now who to award the prize too? I have to look a bit more to see who gave me the first answer that handles 1-n # of categories/ID's.
Go to Top of Page

jaroot
Starting Member

46 Posts
Posted - 2005-06-03 : 10:18:18
Hmm.. Seventhenight gave me the first "elegent" solution, but he did have the hardcoded category values in the solution, which would not account for more category values in the recordset. Michael P gave the first (albiet non-elegent) solution that handles any N# of ID's.

Ahh hell.. since you guys both took time out to figure out the answer, I'll pay ya both.

Send your paypal requests to jaroot@charter.net

I really appreciate you guys helping out this SQL noobie!
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site