![]() The MySQL SUM() is similar to a mathematical sum calculation that finds out the totality of provided table values in database.The result sum value is evaluated on the basis of a condition provided by the values in the next table. SELECT SUM(Unit * CostEach) Product_cost FROM Suppliers INNER JOIN Products ON Supplier_ID WHERE Product_Name = 'Maggie' We will take two tables Products and Suppliers where:Ĭalculating sum by SUM() clause with JOIN clause as follows: The result is zero because there is no row with supplier id 15 in the table Suppliers. SELECT COALESCE (SUM(Unit * CostEach), 0) Output FROM Suppliers WHERE Supplier_ID = 15 If the result set is empty then the SUM() returns NULL value but you can also show zero instead using the COALESCE() with two arguments: HAVING SUM(Unit * CostEach)>200 ORDER BY TotalCost DESC SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID The HAVING clause is used with the SUM() function to filter the sum group where the total cost amount is greater than certain provided integer value as follows: Here the result set is grouped by Supplier id and ordered by total cost calculated in descending order. SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID ORDER BY TotalCost DESC We will use SUM() function with GROUP BY clause to produce the sum output by grouping the values based on a specific column of the table.For example, using the SUM() we will query the total price of each supplier itemwith GROUP BY clause: SELECT SUM(Unit * CostEach)TotalCost FROM Suppliers WHERE Supplier_ID = 11 Įxample #3. Now, we will calculate the total for a supplier item with Supplier id 11 using MySQL SUM() function: SELECT Category, Unit, CostEach FROM Suppliers WHERE Supplier_ID = 11 Suppose, we have a table named Suppliers:įor supplier line items of the supplier id 11we have the following query as: It shows the same sum total because the Price column does not contain any duplicates. SELECT SUM(DISTINCT Price) Total_Price FROM Books So, the SUM() function will ignore the duplicate values and NULL values if available in the column values of table. We can also apply the DISTINCT keyword to find out the unique sum value of unique column values. We are going to use the aggregate SUM() function to fetch the total price of the books from the table quantity using the query below:Īs shown above, the sum has calculated the sum of total price from the book column. To view the contents of this Books table let us query the data: We have a table named Books having some fields defined with columns as BookID, BookName, Language and Price. Let us take a table from the database as a demo table to generate the total sum of column table values. Let us evaluate the MySQL SUM() to show the working of sum with the table columns and rows to get the result total set. Since, the SUM() is an aggregate function so it works on multiple table rows or of a table column to find the sum result to return only a value. The tables are also joined with INNER JOIN and applying the USING keyword to specifycolumn name of the table. Here, the syntax shows a CASE statements with SUM() function evaluated for table columns done for the respective tables. Suppose, if we need to rotate table rows to table columns then, the SUM() function is used together with the MySQL CASE expression which is a type of SUMIF logic illustrated below:įROM TableName1 INNER JOIN TableName2 USING(ColumnName).This will be helpful to find the sum total of values from a large set of data in the database. ![]() We use the MySQL SUM() function along with WHERE clause also to retrieve the result sum of a particular expression that is clarified against a condition positioned after the clause WHERE.The MySQL SUM() function is implemented with SELECT statement and also can be calculated with JOIN clauses. It can be one or multiple separated by comma. Expr_Value can be any column name of the specific table.The MySQL SUM() function disregards the NULL values in the sum calculation in the server.The option DISTINCT keyword is provided to instruct the MySQL SUM() function to evaluate the total of unique values in the set.When the SELECT statement is used with SUM() function that outputs no row, then the MySQL SUM() function will return NULL value but not zero.Hadoop, Data Science, Statistics & others
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |