SQL supports most aggregate functions except one fundamental aggregation to calculate product of values in a column. If you are wondering what could be a scenario where you may have to calculate product of values in a column, here is a business scenario that demands it.
Business Scenario: For the sake of brevity, let’s say we have a table that stores information related to Soccer matches to be played by Team A against a few other teams (say x, y, and z) and the respective probabilities of Team A winning the game as shown below:
Table Name: Team_A_Matches
| OpponentTeam | TeamAWinProbability |
| Team X | 0.2 |
| Team Y | 0.5 |
| Team Z | 0.8 |
Here is the SQL to create the above table:
VALUES(‘Team X’, 0.2);
INSERT INTO Team_A_Matches(OpponentTeam, TeamAWinProbability)
VALUES(‘Team Y’, 0.5);
INSERT INTO Team_A_Matches(OpponentTeam, TeamAWinProbability)
VALUES(‘Team Z’, 0.8);
Mathematical Translation of the Challenge: The requirement now is to calculate the combined probability of Team A winning the matches arranged against all other teams. Here, you should be applying multiplication rule of probability for independent events, because the matches are arranged in a way that results of the matches are not dependent on one another. i.e., Probability of Team A winning all matches = Probability of Team A winning against (Team X ∩ Team Y ∩ Team Z) = Probability (Team A winning against Team X) * Probability (Team A winning against Team Y) * Probability (Team A winning against Team Z). This means you should be calculating product of values in the ‘TeamAWinProbability’ column of the table in our scenario.
Solution: Now for a moment just focus on calculating product of values in a column alone. Since, SQL doesn’t have a pre-defined aggregate function to calculate product of values in a column, you need to think of an alternate mathematical way of calculating product of values that can be translated into SQL.
Here is the fundamental concept of logarithms in math:
A fundamental equation that mathematically defines logarithms is:
The above fundamental equation of logarithms when translated into English: Logarithm of a to the base b is nothing but what is the number to which b is to be raised/exponentiated to get a; and the answer is x.
Another fundamental equation in logarithms is (
Product rules of Logarithms):
The above when translated into English: Logarithm of product of numbers is equal to the sum of logarithms of individual numbers.
From the above two equations, below equation is a valid one too:
Going by natural logarithms where a mathematical constant (aka Euler’s number) denoted by ‘e’ is the base of the natural logarithm, this equation translates to :
SQL Translation of the Equation: Note that SQL supports aggregate function named EXP that computes ‘e’ to the power of ‘x’, where x is a number of your choice. This function is also called natural exponentiation function.
SQL also has a function named LOG that calculates natural logarithm of a given number. Oracle supports this same function with the name LN.
Using these two functions, the SQL transalation of this equation becomes: EXP(SUM(LOG(TeamAWinProbability)))
Oracle’s SQL version: EXP(SUM(LN(TeamAWinProbability)))
So, the below SQL gives the combined probability of Team A winning against Team X, Team Y and Team Z:
SELECT Round(EXP(SUM(LOG(TeamAWinProbability))),2) AS TeamAWinProbAgainstXYZ
FROM Matches;
and the result of this SQL for this scenario will be .08 (which is same as the result of 0.20.50.8)
So, the learning is –
when you need to calculate product of values in a column in SQL, simply calculate natural exponentiation of sum of natural logarithms of the values in the column.
If you happen to learn something from this, all I would request you is to share it further using as many of the below social platforms as you could and help me spread the learnings.
Happy learning and engage me for any advanced SQL analytics trainings in your organizations.
