This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to get the multiplication for each Group.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
GroupName GroupValue ---------- ----------- A 10 B 15 C 20 D 30 B 30 C 60 D 40 D 50 A 70 B 40 C 10 B 90 |
Expected Output:
|
1 2 3 4 5 6 |
GroupName GroupValue ---------- ---------------------- A 700 B 1620000 C 12000 D 60000 |
Sample table:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_GroupNumber (GroupName VARCHAR(10), GroupValue INT) INSERT INTO tbl_GroupNumber VALUES ('A',10),('B',15),('C',20),('D',30) ,('B',30),('C',60),('D',40),('D',50) ,('A',70),('B',40),('C',10),('B',90) GO |
Solution 1:
|
1 2 3 4 5 6 |
SELECT GroupName ,Exp(Sum(IIf(Abs(ISNULL(GroupValue,1))=0,0,Log(Abs(ISNULL(GroupValue,1))))))*IIf(Min(Abs(ISNULL(GroupValue,1)))=0,0,1)* (1-2*(Sum(IIf(ISNULL(GroupValue,1)>=0,0,1)) % 2)) AS GroupValue FROM tbl_GroupNumber GROUP BY GroupName |
Solution 2:
|
1 2 3 4 5 |
SELECT GroupName ,EXP(SUM(LOG(GroupValue))) AS GroupValue FROM tbl_GroupNumber GROUP BY GroupName |
Please try the different solution for this puzzle and share it via comment...
