SQL SUM

5 Key excerpts on "SQL SUM"

• 

  eBook - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  The basic syntax is as follows: SUM(expression_to_be_added_together) 196 Chapter 6 For example, the following code adds up all the values in the CategoryId column for all records in the Category table: SELECT SUM(CategoryId) FROM Category; The result of the statement is 28. Given that the CategoryId column is simply a primary key column, the result is not that meaningful. SUM() can also contain expressions and calculations: SELECT SUM(CategoryId * MemberId) FROM FavCategory WHERE CategoryId < 4; The preceding code sums up CategoryId multiplied by MemberId for each record and then sums up the results for all records where CategoryId is less than 4; the final answer is 159. You can use SUM() only with numerical fields. The SUM() function counts only actual values; NULL values are ignored. As with COUNT(), the SUM() function contains a DISTINCT option, so that your results include the sum of only distinct values. The Try It Out that follows shows you how to use the SUM() function. Try It Out Using the SUM() Function 1. The club chairperson is feeling quite generous and has decided to buy a copy of all the films available on DVD. Use the following SQL to calculate the total cost, including sales tax of 10%: SELECT SUM(DVDPrice * 1.1) FROM Films WHERE AvailableOnDVD = ‘Y’; 2. After seeing how much each DVD costs, the chairperson has a change of mind and wants to buy only the films available on DVD that cost less than $10. Using the following SQL, calculate the total cost, including sales tax of 10%: SELECT SUM(DVDPrice * 1.1) FROM Films WHERE AvailableOnDVD = ‘Y’ AND DVDPrice < 10; How It Works First, you created the SQL to extract the total price for all DVDs in the database: SELECT SUM(DVDPrice * 1.1) FROM Films WHERE AvailableOnDVD = ‘Y’; Running the query provides the result 119.702, except on MySQL, where it returns 118.80.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  CHAPTER 21 Aggregate Functions

  O 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.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Greenplum - Architecture and SQL

  • 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 Ali

  CSUM

  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,

  SUM(Daily_Sales) OVER (ORDER BY   Sale_Date

  ROWS UNBOUNDED PRECEDING) AS CsumAnsi FROM  Sales_Table WHERE Product_ID BETWEEN 1000 and 2000 ;

  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.

  CSUM – Rows Unbounded Preceding Explained

  SELECT    Product_ID , Sale_Date, Daily_Sales, SUM(Daily_Sales) OVER (ORDER BY  Sale_Date

  ROWS UNBOUNDED PRECEDING )  AS CsumAnsi

  FROM      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).

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Oracle SQL

  Jumpstart with Examples

  • Gavin JT Powell, Carol McCullough-Dieter(Authors)
  • 2004(Publication Date)
  • Digital Press

    (Publisher)

  However, statistical functions are appropriate to both aggregation and analytics. Analytic Functions . Functions that summarize data into multiple values based on a sliding window of rows using an analytic clause. These structures are used most frequently in data warehousing to analyze historical trends in data. For example, the statistical STD-DEV function can be used as an analytic function that returns stan-dard deviations over groups of rows. SPREADSHEET Clause Functions . SPREADSHEET clause func-tions enhance the SPREADSHEET clause. These functions are cov-ered later in this chapter in the section on the SPREADSHEET clause. Let’s begin with aggregate functions. 11.2.1 Aggregate Functions An aggregate function applies an operation to a group of rows returning a single value. A simple example of an aggregate function is in the use of the SUM function as shown following. See the result in Figure 11.2. SELECT SUM(AMOUNT_CHARGED), SUM(AMOUNT_PAID) FROM STUDIOTIME; 238 11.2 Types of Group Functions What are the available aggregate functions and how are they used? Let’s go through the definitions. Functions have been divided into different sections. 11.2.1.1 Simple Summary Functions AVG(expression ). The average. COUNT(*|expression) . The number of rows in a query. MIN(expression ). The minimum . MAX(expression ). The maximum. SUM(expression) . The sum. An expression can be anything: a column name, a single-row function on a column name, or simple calculations such as two columns added together. Anything you might place in the SELECT clause can be used as an expression within a group function. 11.2.1.2 Statistical Function Calculators STDDEV(expression ). The standard deviation is the average differ-ence from the mean. The mean is similar to the average. VARIANCE(expression) . The variance is the square of the standard deviation and thus the average squared difference from the mean, or the average deviation from the mean.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  Which employees are Database Administrators or took the Data Science course? 14. Which Database Administrators did not take the Data Science course? 15. Which Database Administrators took the Data Science course? 126 SQL: AN INTRODUCTION TO QUERYING 5.5 Aggregation and Grouping Aggregate operators (min, max, avg, sum, count) provide a single result that aggregates the detailed data. Many important queries over the data in a database involve aggregation of results, such as queries involving minimum, maximum, sum, count, and average. These operators provide a single result that ''aggregates'' the detailed data. SQL provides inherent support in the language for aggrega- tion constructs. In the previous coverage of the formal query languages of relational algebra and relational calculus, queries involving minimum/maximum and a limited form of counting (more than one/only one) illustrated the use of logic to answer these types of questions. This section explores how to analyze data using SQL’s aggregation and grouping capabilities. Consider the following query that finds the minimum, maximum, and average employee salary as well as the sum of all salaries and a count of the number of employees in the database. sql_SalariesAggregationExample: √ select min(E.eSalary), max(E.eSalary), avg(E.eSalary), sum(E.eSalary), count(*) from employee E This query returns a single tuple with five attribute values. The min, max, avg and sum aggregate functions return the corresponding value for its argument. The count operator with * as an argument counts the number of tuples in the result. Note that the name of the resulting aggregate attributes is product dependent. Therefore, renaming each attribute with a descriptive name using the as keyword is usually recommended, as shown in the following sql_SalariesAggregationExampleRenaming query.

  Sign up to read

  Learn more about book