![]() 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.
0 Comments
Leave a Reply. |