Suppose that we have one table named workers consisting of the following records in it that are retrieved by executing a simple select query on that table. Even in some scenarios, the data from a single table need to be manipulated to get summarized data. When we have complex tables and relations between multiple tables, we have to query those tables using joins to retrieve data, usually for reporting purposes that consist of summarized data. We can even use the existing functions such as AVG() and COUNT() inside the SUM() function. Let us consider one example we will calculate the total of all the columns after they are multiplied by 10 and added by 1. We can use the expressions in the SUM() function to consider the value evaluated by calculating each of the formula or expression values containing column value to calculate the total value. The output of both of the above queries is as shown below: The output will be different from that of the first query without a distinct function because internally, the calculation of total value by SUM() function will only consider the repeated column values, such as 100 and 150, only once. If we use SELECT SUM(num) FROM numbers statement to calculate the total value of num column, then each of the values will be considered while calculating the total value if we use the following statement. Suppose that we insert some more records in the numbers table using the following query statement. We can use the distinct function in the SUM() function to consider the column’s repetitive values only once while calculating the total value. Now, we will calculate the total of num column of numbers table using SUM() function using the following query statement.Įxample #2 – Using the distinct function. Now, we will insert the above records in the table. We will use the following query statement to create our table. Let us create one simple table named numbers and store the num column value in it. We will calculate the total value of SQL numbers using the SUM() function. Given below are the examples of SQL SUM(): Example #1 – Using a single column.įirst, consider a simple example that we used above to see the working of the SUM() function. Then total function will internally calculate 50 + 100 + 150 + 200 which evaluates to 500. For example, consider that we have to find out a total of 50, 100, 150, and 200. The SUM() function collects all the values of the expression mentioned in it and adds them up to find out the final total value.
When where clause is used, only filtered out data is considered for the SUM() function calculation. One optional thing is the use of a where clause to mention the conditions and restrictions that the records of the table should fulfil to consider that record’s column value for calculating the total value. The use of the FROM table name clause is required. The table_name is the name of the table from which you want to retrieve the records and calculate the total value from one of their columns. Where expression can be any name of the column of the table or a formula built up using column names and static literal values or variables. Hadoop, Data Science, Statistics & others
0 kommentar(er)
