Computer Science
SQL SUM
SQL SUM is a function used in SQL (Structured Query Language) to calculate the sum of values in a specified column of a table. It is commonly used in data analysis and reporting to quickly obtain the total value of a particular data set.
Written by Perlego with AI-assistance
Related key terms
6 Key excerpts on "SQL SUM"
eBook - ePubLearn T-SQL From Scratch
An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server and Writing T-SQL Queries Efficiently
- Brahmanand Shukla(Author)
- 2021(Publication Date)
- BPB Publications(Publisher)
column 1 + column 2 , and so on.For example, if we have data as shown in the following table and if we need to perform summation then we’ve only two options – perform addition of every single row or make use of aggregate function SUM. In the following table, we’ve only 10 rows . Assume if you’ve thousands or millions of rows then is it possible to add those individually? As we already discussed earlier, the answer is No :Amount 1000 2000 5000 800 200 10 5000 2500 1200 4300 Table 5.1: Table with Amount columnWe can use the SUM function and easily perform summation of all the rows of Amount column and get the single meaningful value as 22010 .The syntax is as follows:SUM (<column name>)The example is as follows:The following is the query to get the summation of all the purchase orders for the period 1 st April 2019 to 30th June 2019 :SELECT SUM ( Amount)FROM PurchaseOrderWHERE TransactionDate BETWEEN '2019-04-01' AND '2019-06-30'Running the query will return the result as shown in the following screenshot:Figure 5.6: Output of SUM, no column name is displayed in the resultIf you’ll notice the column name in the result pane is coming as (No column name) as shown in the preceding screenshot. Do you wonder why? Because we’ve applied the aggregate function on the column and we’ve not specified the column name for the aggregated column.We can provide the custom name to such columns using AS keyword as shown in the following example.If you’ll also notice the column name TotalAmount has been specified within the square bracket [] . Even if you’ll not specify it within the bracket, the query will work. But if you would like to name it as Total Amount it won’t due to space in the name:SELECT SUM ( Amount) AS [TotalAmount]FROM PurchaseOrderWHERE TransactionDate BETWEEN '2019-04-01' AND '2019-06-30'AS keyword is used to provide the custom name to the columns. You can even use AS keyword with all the columns to specify the customer name to it, irrespective of whether the aggregate function is specified on the column or not.
eBook - ePubQuerying SQL Server
Run T-SQL operations, data extraction, data manipulation, and custom queries to deliver simplified analytics (English Edition)
- Adam Aspin(Author)
- 2022(Publication Date)
- BPB Publications(Publisher)
- When you drag and drop table or field names into the query window, SSMS automatically wraps them in square brackets. This has no effect on the query and is simply a way of ensuring that if table and field names do not contain any spaces or nonstandard characters, then the query is nonetheless likely to work correctly.
Conclusion
In this chapter, you saw some of the ways that SQL can be used to carry out simple arithmetic. You learned how to perform basic math using the data in fields as well as values that you add to your code to perform calculations across multiple rows and columns in a few lines of code.You also saw how to pre-empt errors when the source data contains NULLs or when attempting to divide by zero in a calculation.All in all, you have already made vast strides toward the basic analysis of SQL Server data. With this knowledge safely acquired, it is time to move on to the next step—aggregating data to deliver totals, subtotals, and other simple calculations. This is what you will be looking at in the next chapter.Core Knowledge Learned in This Chapter
These are the fundamental keywords you have learned to use in this chapter:Concept Description + This is the arithmetical addition symbol; it adds two elements (fields or values). - This is the arithmetical subtraction symbol; it subtracts tw elements (fields or values). * This is the arithmetical multiplication symbol; it multiplies two elements (fields or values). / This is the arithmetical division symbol; it divides two elements (fields or values). ISNULL() This converts NULL (or empty) values into a value that can be used in your calculations. Parentheses Parentheses are used in calculations to force certain parts of a formula to be calculated before others. 
eBook - ePubJoe Celko's SQL for Smarties
Advanced SQL Programming
- Joe Celko(Author)
- 2010(Publication Date)
- Morgan Kaufmann(Publisher)
CHAPTER 21 Aggregate FunctionsO NE OF THE MAJOR purposes of a database system is to turn data into information. This usually means doing some statistical summary from that data. Descriptive statistics measure some property of an existing data set and express it as a single number. Though there are very sophisticated measures, most applications require only basic, well- understood statistics. The most common summary functions are the count (or tally), the average (or arithmetic mean), and the sum (or total).This minimal set of descriptive statistical operators is built into the SQL language, and vendors often extend these options with others. These functions are called set functions in the ANSI/ISO SQL standard, but vendors, textbook writers, and everyone else usually call them aggregate functions, so I will use that term.Aggregate functions first construct a column of values as defined by the parameter. The parameter is usually a single column name, but it can be an arithmetic expression with scalar functions and calculations. Pretty much the only things that cannot be parameters are other aggregate functions (e.g., SUM (AVG (x)) is illegal) and subqueries (e.g., AVG (SELECT coll FROM Some Table WHERE …) is illegal). A subquery could return more than one value, so it would not fit into a column, and an aggregate function would have to try to build a column within a column.Once the working column is constructed, all the NULLs are removed and the function performs its operation. As you learn the definitions I am about to give, stress the words’ known values to remind yourself that the NULLs have been dropped.There are two options, ALL and DISTINCT, that are shown as keywords inside the parameter list. The keyword ALL is optional and is never really used in practice. It says that all the rows in the working column are retained for the final calculation. The keyword DISTINCT is not optional in these functions. It removes all redundant duplicates values from a working column before the final calculation. Let’s look at the particulars of each aggregate function.
eBook - ePub- Joshua N. Milligan, Blair Hutchinson, Mark Tossell, Roberto Andreoli(Authors)
- 2022(Publication Date)
- Packt Publishing(Publisher)
Integrating Advanced Features: Extensions, Scripts, and AI .The Total function
The Total function deserves its own category because it functions a little differently from the others. Unlike the other functions, which work on the aggregate table in the cache, Total will re-query the underlying source for all the source data rows that make up a given partition. In most cases, this will yield the same result as a window function.For example, Total(SUM([Sales])) gives the same result as Window_Sum(SUM([Sales])) , but Total(AVG([Sales])) will almost certainly give a different result from Window_AVG(SUM([Sales])) because Total is giving you the actual average of underlying rows, while the Window function is averaging the sums, as can be seen here:Figure 6.24: The difference between WINDOW_AVG() and TOTAL(AVG)As you can see, WINDOW_AVG() gives you the average of the sums shown by the Sales line. TOTAL(AVG()) gives the overall total average of the underlying data set (in other words, the average sales line is $950).In this section, we have looked at a number of table calculation functions. These will give you the building blocks to solve all kinds of practical problems and answer a great many questions. From ranking to year-over-year comparisons, you now have a foundation for success. Let’s now move on to some practical examples.Practical examples
Having looked at some of the essential concepts of table calculations, let’s consider some practical examples. We’ll look at several examples, although the practical use of table calculations is nearly endless. You can do everything from running sums and analyzing year-over-year growth to viewing percentage differences between categories, and much more.Year over year growth
Often, you may want to compare year over year values. How much has our customer base grown over the last year? How did sales in each quarter compare to sales in the same quarter last year? These types of questions can be answered using Year over Year Growth
- Jonathan Eckstein, Bonnie R. Schultz(Authors)
- 2017(Publication Date)
- Wiley(Publisher)
To give a simple example of using such functions, suppose we want to know the total number of items ordered over the entire history covered by the database. To display this information, we may write the query:SELECT Sum(Quantity) FROM ORDERDETAIL;This query adds up the Quantity field over all rows of the ORDERDETAIL table and produces (for the particular data in the example from Chapter 7 ) the single result “163.” It is also possible to specify multiple aggregation operations in the same query. By way of illustration:SELECT Sum(Quantity), Avg(Quantity), Max(OrderID) FROM ORDERDETAIL;displays a single row of information showing the total number of items ordered, the average number of items per order detail line, and the alphabetically last OrderID value for which there are any order detail records (perhaps not a particularly useful piece of information).We may also apply aggregation functions to expressions rather than to just simple fields. For example, if we want to know the average difference (over all products) between the units in stock and the units on order, we can use the query:SELECT Avg(UnitsinStock ‐ UnitsonOrder) FROM PRODUCT;Here, we are using the Avg aggregation function instead of the Sum aggregation function and a more complicated expression than a simple field as its argument. This query produces the single value 29.6451612903, meaning that the number of units in stock averages about 30 units higher than the number of units on order.3Now suppose that we want to see the average order quantity per order line for products whose ProductID
eBook - ePub- Tom Coffing, Leona Coffing(Authors)
- 2015(Publication Date)
- Coffing Publishing(Publisher)
Chapter 16 – OLAP Functions
“Don’t count the days, make the days count.” - Mohammad AliCSUM
This ANSI version of CSUM is SUM () Over. Right now, the syntax wants to see the sum of the Daily_Sales after it is first sorted by Sale_Date. Rows Unbounded Preceding means to start calculating from the first row, and continue calculating until the last row. This Rows Unbounded Preceding makes this a CSUM. The ANSI Syntax seems difficult, but only at first.CSUM – The Sort Explained
SELECT Product_ID , Sale_Date, Daily_Sales,The first thing the above query does before calculating is SORT all the rows by Sale_Date. The Sort is located right after the ORDER BY.SUM(Daily_Sales) OVER (ORDER BY Sale_DateROWS UNBOUNDED PRECEDING) AS CsumAnsi FROM Sales_Table WHERE Product_ID BETWEEN 1000 and 2000 ;CSUM – Rows Unbounded Preceding Explained
SELECT Product_ID , Sale_Date, Daily_Sales, SUM(Daily_Sales) OVER (ORDER BY Sale_DateROWS UNBOUNDED PRECEDING ) AS CsumAnsiFROM Sales_Table WHERE Product_ID BETWEEN 1000 and 2000 ;The keywords Rows Unbounded Preceding determines that this is a CSUM. There are only a few different statements and Rows Unbounded Preceding is the main one. It means start calculating at the beginning row, and continue calculating until the last row.CSUM – Making Sense of the Data
SELECT Product_ID , Sale_Date, Daily_Sales, SUM(Daily_Sales) OVER (ORDER BY Sale_Date ROWS UNBOUNDED PRECEDING) AS SUMOVER FROM Sales_Table WHERE Product_ID BETWEEN 1000 and 2000 ;The second “SUMOVER” row is 90739.28. That is derived by the first row’s Daily_Sales (41888.88) added to the SECOND row’s Daily_Sales (48850.40).CSUM – Making Even More Sense of the Data
SELECT Product_ID , Sale_Date, Daily_Sales, SUM(Daily_Sales) OVER (ORDER BY Sale_Date ROWS UNBOUNDED PRECEDING) AS SUMOVER FROM Sales_Table WHERE Product_ID BETWEEN 1000 and 2000 ;The third “SUMOVER” row is 138739.28. That is derived by taking the first row’s Daily_Sales (41888.88) and adding it to the SECOND row’s Daily_Sales (48850.40). Then, you add that total to the THIRD row’s Daily_Sales (48000.00).
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
