![]() Why? Because ‘2’ appears twice in the quantity column for items in the clothing category. However, the query returns a 3 for the total quantity of clothing in stock. If you look at the database, you’ll see that there are five items of clothing in stock: name To do this, use the SUM function with the DISTINCT keyword in the argument. The SQL SUM() function can also sum up unique (i.e. Of course, you can also group records and calculate sums on more than one column. The ‘toys’ category has one product with a NULL in the quantity column, so the remaining values in this category are summed. This is because all the rows that have an ‘IT’ value in the category column have a NULL in the quantity column. Notice that for the IT category the calculated sum is NULL. ![]() Finally, remember that the GROUP BY clause must always come after FROM. Adding the GROUP BY clause means that products with the same value in the category column are put into one group the sum is calculated for each group separately. Next is the SUM() function, which sums up the quantity values. In this case, the category column is in the SELECT because we want to see the category for which the sum is calculated. In the query below, you can see how many products are in each category:Īnd the result returned by this query is: category If you’re not familiar with GROUP BY, I suggest reading Using GROUP BY in SQL or How Does SQL GROUP BY Work? before proceeding with this example. With GROUP BY, the summed values are computed for a group of rows. Usually, you use the SUM function with the GROUP BY clause. The total values calculated for each product are summed and the grand total of their values is returned the total_sum is 1520, as you can see in the result. In this case, the argument in the SUM() function is an expression that calculates the total value for each product: the quantity of the product (the value in the column quantity) multiplied by its price (the value in the column price). This is the query:Īnd the result returned by it is: total_value Next, we’ll consider an example that uses an expression as the SUM() argument. Example 2: Using SUM() with an Expression Note that there is only one row in the result: all the individual rows were “collapsed” by SUM() – their details are not available in the result. ![]() If one of the rows has a NULL in this column, SUM() omits this row from the calculation. the alias) is sum_quantity.Īs you see, the sum of values in the quantity column in the table productis 7. The SUM() function adds all values from the quantity column and returns the total as the result of the function. In this query, we use SUM() alone in the SELECT statement. If you want to sum values stored in one column, use SUM() with that column’s name as the argument. The product table stores data we’ll use to calculate the sum of given values. I will explain this issue more in the next section.įirst, let’s get to know the data on which we will build our SQL SUM() queries. The DISTINCT keyword is optional when it’s used, SUM() only adds non-repeating values. The values provided in the argument are summed up and returned by the SUM() function. As you see, this function requires only one argument: a column name or an expression.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |