March 19, 2010 at 3:55 pm
Hello!
I want to write a query (if is possible) or create a procedure for the following problem. I have a table as follows:
[mytable]
ID Name Kf Tf
1TestName1 2.5000True
2TestName1 2.0000True
3TestName1 1.5000True
4TestName1 2.2000False
5TestName1 1.7000True
6TestName1 3.0000True
I'm using the following query to get multiply of Kf column where Tf is True:
select exp(sum(log(kf))) from mytable where Tf='True'
This works. What i need is how to get the sum of that function for all possible combination, where Tf is True, based in a variable.
For example. Let's say this variable is 4.
All possible combination should be:
1TestName1 2.5000True
2TestName1 2.0000True
3TestName1 1.5000True
5TestName1 1.7000True
Multiple Value = 12.75
1TestName1 2.5000True
2TestName1 2.0000True
3TestName1 1.5000True
6TestName1 3.0000True
Multiple Value = 22.5
1TestName1 2.5000True
2TestName1 2.0000True
5TestName1 1.7000True
6TestName1 3.0000True
Multiple Value = 25.5
2TestName1 2.0000True
3TestName1 1.5000True
5TestName1 1.7000True
6TestName1 3.0000True
Multiple Value = 15.3
At the end i need to multiply each value by a fixed number (let's say 50) and have a total sum (Example: (12.75*50)+(22.75*50)+(25.5*50)+(15.3*50)=TOTAL ) All i need is this total sum...
Help!
March 19, 2010 at 5:38 pm
I'm not sure about your requirements, so this is just a hint. For a combination of four values you can do something like this:
DECLARE @MyTable TABLE (ID INT, Name VARCHAR(100), Kf FLOAT, Tf VARCHAR(5))
INSERT INTO @MyTable
VALUES
(1, 'TestName1', 2.5000, 'True'),
(2, 'TestName1', 2.0000, 'True'),
(3, 'TestName1', 1.5000, 'True'),
(4, 'TestName1', 2.2000, 'False'),
(5, 'TestName1', 1.7000, 'True'),
(6, 'TestName1', 3.0000, 'True'),
(7, 'TestName2', 2.5000, 'True'),
(8, 'TestName2', 2.0000, 'True'),
(9, 'TestName2', 3.0000, 'False'),
(10, 'TestName2', 1.0000, 'True'),
(11, 'TestName2', 2.0000, 'True'),
(12, 'TestName3', 2.0000, 'True')
SELECT
T1.Name,
-- SUM(EXP(LOG(T1.Kf) + LOG(T2.Kf) + LOG(T3.Kf) + LOG(T4.Kf)) * 50),
SUM((T1.Kf * T2.Kf * T3.Kf *T4.Kf) * 50)
FROM
@MyTable T1
LEFT JOIN
( SELECT ID, Name, Tf, Kf FROM @MyTable) T2 ON T2.ID > T1.ID AND T2.Name = T1.Name AND T2.Tf = 'True'
LEFT JOIN
( SELECT ID, Name, Tf, Kf FROM @MyTable) T3 ON T3.ID > T2.ID AND T3.Name = T2.Name AND T3.Tf = 'True'
LEFT JOIN
( SELECT ID, Name, Tf, Kf FROM @MyTable) T4 ON T4.ID > T3.ID AND T4.Name = T3.Name AND T4.Tf = 'True'
GROUP BY
T1.Name
I assume you want to group on Name. If there are less then 4 values within a certain Name with Tf=True then the result is NULL. If you want to vary on the number of values in the combination you have to use dynamic SQL. I leave that as an excercise to the reader.
Peter
Edit: replaced unnecessary EXP/LOG expression
March 20, 2010 at 6:29 am
Hello Peter!
Thx for you response.
I think you misunderstood the problem. There is no group by "name", because all the records are unique. It means "name" can not be repeated twice, so "group by" it doesn't make sense.
You can check here to understand what i'm asking for http://www.mathsisfun.com/combinatorics/combinations-permutations.html
What i want is: Combinations without Repetition, ignoring order.
It's very easy to calculate how many combinations can be formed, but what i'm asking for is the sum of aggregate multiply of Kf column for every combination, and properly there is the difficulty...
My idea is to create a table like this:
combination# TotKf
combination1 12.75
combination2 22.5
combination3 25.5
combination4 15.3
And get the sum of TotKf*50 column... The problem is how to generate the above table with a single query...
Regards!
March 20, 2010 at 8:39 am
Well, then simply remove the group by and use an inner join to get all combinations of 4 values.
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) CombinationNo,
T1.ID, T2.ID, T3.ID, T4.ID,
T1.Kf * T2.Kf * T3.Kf * T4.Kf TotKf
FROM
@MyTable T1
JOIN
(SELECT ID, Name, Tf, Kf FROM @MyTable) T2 ON T2.ID > T1.ID AND T2.Tf = 'True'
JOIN
(SELECT ID, Name, Tf, Kf FROM @MyTable) T3 ON T3.ID > T2.ID AND T3.Tf = 'True'
JOIN
(SELECT ID, Name, Tf, Kf FROM @MyTable) T4 ON T4.ID > T3.ID AND T4.Tf = 'True'
WHERE
T1.Tf = 'True'
Note that 1, 3, 5, 6 is also a combination. It is missing in your example.
Peter
March 20, 2010 at 9:21 am
Here's a recursive solution which allow you to vary in combination sizes easily.
DECLARE @MyTable TABLE (ID INT, Name VARCHAR(100), Kf FLOAT, Tf VARCHAR(5))
INSERT INTO @MyTable
VALUES
(1, 'TestName1', 2.5000, 'True'),
(2, 'TestName1', 2.0000, 'True'),
(3, 'TestName1', 1.5000, 'True'),
(4, 'TestName1', 2.2000, 'False'),
(5, 'TestName1', 1.7000, 'True'),
(6, 'TestName1', 3.0000, 'True')
DECLARE @Size INT = 4
;WITH Combinations AS
(
SELECT
1 Size, ID, CAST(ID AS VARCHAR(MAX)) IDs, Kf TotalKf
FROM
@MyTable
WHERE
Tf = 'True'
UNION ALL
SELECT
Size + 1, T.ID, C.IDs + ',' + CAST(T.ID AS VARCHAR), C.TotalKf * T.Kf
FROM
Combinations C
JOIN
@MyTable T ON T.ID > C.ID
WHERE
T.Tf = 'True' AND Size < @Size
)
SELECT
ROW_NUMBER() OVER (ORDER BY IDs) CombinationNo,
IDs, TotalKf
FROM
Combinations
WHERE
Size = @Size
ORDER BY
1
OPTION (MAXRECURSION 0)
Peter
March 20, 2010 at 10:04 am
:blink: I had just about finished mine when I saw the new posts. Gah!
Oh well, it was too much work to not post it, so here it is:
Setup
DECLARE @Table
TABLE (
id INTEGER NOT NULL PRIMARY KEY,
name CHAR(9) NOT NULL,
kf DECIMAL(9,6) NOT NULL,
tf BIT NOT NULL
);
INSERT @Table (id, name, kf, tf)
VALUES (1, 'TestName1', 2.5, 'True'),
(2, 'TestName1', 2.0, 'True'),
(3, 'TestName1', 1.5, 'True'),
(4, 'TestName1', 2.2, 'False'),
(5, 'TestName1', 1.7, 'True'),
(6, 'TestName1', 3.0, 'True');
Solution:
DECLARE @Combinations INTEGER = 4;
WITH Selection
AS (
SELECT iteration = 1,
T1.id,
list = hierarchyid::Parse('/' + CONVERT(VARCHAR(12), T1.id) + '/'),
running_total = CONVERT(DECIMAL(9,6), T1.kf)
FROM @Table T1
WHERE tf = 'true'
UNION ALL
SELECT S.iteration + 1,
T.id,
hierarchyid::Parse(S.list.ToString() + CONVERT(VARCHAR(12), T.id) + '/'),
CONVERT(DECIMAL(9,6), S.running_total * T.kf)
FROM Selection S
JOIN @Table T
ON T.id > S.id
WHERE T.tf = 'true'
AND S.iteration < @Combinations
)
SELECT combination = S.list.ToString(),
S.running_total
FROM Selection S
WHERE S.list.GetLevel() = @Combinations
ORDER BY
S.list;
Output:
combination running_total
/1/2/3/5/ 12.750000
/1/2/3/6/ 22.500000
/1/2/5/6/ 25.500000
/1/3/5/6/ 19.125000
/2/3/5/6/ 15.300000
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 10:28 am
Sorry, Paul. But your solutions looks interesting. I have never used Hierarchyid before, so that leaves me something to study on.
Peter
March 20, 2010 at 10:33 am
Peter Brinkhaus (3/20/2010)
Sorry, Paul. But your solutions looks interesting. I have never used Hierarchyid before, so that leaves me something to study on.
Ha! No worries - I should have been quicker!
There is a much better example of using hierarchyid on another thread in this topic - and it was part of the reason I was late for this one. It's the one about binary trees...http://www.sqlservercentral.com/Forums/Topic886522-392-1.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 10:42 am
Before I follow your link to that binary tree thread, I must admit I posted a solution on counting nodes in a binary tree yesterday. I was an awkward one but I couldn't come up with anything better given the restrictions on recursive CTE's. I suppose you did a must better job.
Peter
March 20, 2010 at 10:43 am
Peter Brinkhaus (3/20/2010)
Before I follow your link to that binary tree thread, I must admit I posted a solution on counting nodes in a binary tree yesterday. I was an awkward one but I couldn't come up with anything better given the restrictions on recursive CTE's. I suppose you did a must better job.
Same thread, yes. Should have remembered you already posted - don't know where my mind is tonight!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 3:59 pm
Thank you so much Peter and Paul. I tested Peter solution and it works perfectly.
I wanted to know if hierarchyid works in SQL 2005, because i get "Type hierarchyid is not a defined system type" in SQL 2005
Again, thank you so much!
EDIT: it looks it works only in SQL 2008.
March 20, 2010 at 4:25 pm
Hierarchyid was a new data type in SQL 2008. You posted your question in a SQL 2008 forum, so you got a SQL 2008 solution from Paul. Post your questions to a SQL 2005 forum if SQL 2005 compatibility is a requirement. Anyway, glad the solutions worked out.
Peter
March 20, 2010 at 10:19 pm
Peter Brinkhaus (3/20/2010)
Hierarchyid was a new data type in SQL 2008. You posted your question in a SQL 2008 forum, so you got a SQL 2008 solution from Paul. Post your questions to a SQL 2005 forum if SQL 2005 compatibility is a requirement. Anyway, glad the solutions worked out.
Thanks Peter.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply