This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an aggregate query for calculating Multiplication for each group in SQL Server. We are using an aggregate function like MAX(), SUM(). But there is no any aggregate function for Multiplication.
We can use EXP/LOG expression for this exercise. EXP returns the exponential value and LOG returns the natural logarithm of the specified float expression.
Please check the below demonstration:
Create a table with sample data:
|
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 2 3 4 5 |
SELECT GroupName ,EXP(SUM(LOG(GroupValue))) AS GroupValue FROM tbl_GroupNumber GROUP BY GroupName |
Result:
|
1 2 3 4 5 6 |
GroupName GroupValue ---------- ---------------------- A 700 B 1620000 C 12000 D 60000 |
Leave a Reply