SQL Sum: Adding Up All Your Values

SQL Server has a number of aggregate functions. One such function is the “sum()” function. This function adds up two or more columns to give you a total added value for all these columns. The columns can be integers, floats, decimals or money data types. You can use the sum function to add up and display results or you can add up and store values in another database table column.

New to SQL and writing queries? Learn how to write SQL queries for your applications at Udemy.com.

Identifying Columns for the Sum Function

Any numeric column can be used with the sum function.  To identify columns you can use for the sum function, you need to look at your tables and the defined data types. For instance, in SQL Server, if you right click any database table, you can select “Design” to see a list of column definitions. Other databases such as MySQL and Oracle have GUI software to help you with table design.

Using the Sum Function

After you know the columns you want to use, you can now run the sum function on your tables. For instance, you might have an orders table where your customers ordered a quantity of products. The following code is an example of summing up quantities for each of your orders:

select sum(quantity) from orders

Run this statement and view your results. In this case, you only see a list of added quantities. This isn’t very helpful from a reporting status. The following code gives you added quantities, but it has a column name for the summed quantities:

select sum(quantity) as TotalQuantity from orders

In the above statement, your added quantities show up with a “TotalQuantity” column header. Of course, you probably don’t want just a list of quantities. Even if this was for a report, it doesn’t help all that much. You probably want to know how much each customer ordered. To perform this type of search, you need to use two more phrases on your existing query.

Learn how to work with SQL and query your database at Udemy.com.

The first step is to add a “where” clause to the query. The “where” clause filters the amount of records returned. This means that instead of returning all quantities, which is what happens in the above statements, you return only records you want to see. For instance, suppose you only want to see a list of customer quantities for orders placed within 7 days. The following code is an example of how to do that:

select sum(quantity) as TotalQuantity from orders where orderdate >= getdate() – 7

In the above statement, it’s assumed that you keep the date of the order in the “orderdate” column. The “getdate()” function gets the current system date and time. The “- 7” part of the clause subtracts 7 days from the current date. Therefore, the only records returned are orders with an order date greater than 7 days ago.

With just the above code, you get a quantity for every record in the database orders table. This query assumes you keep a quantity in your table. Some database administrators will force you to avoid this method and only link to products ordered, which would then have a quantity in this section’s table.

This means that you’ll get a summed up quantity for every order, but suppose you want to see quantities based on a customer. Normally, you have a customer Id stored in the orders table to link the customer with the order. The following code shows you how to group records by customer id:

select customerId, sum(quantity) as TotalQuantity from orders where orderdate >= getdate() – 7

The above code shows you a summed up quantity for each customer Id. However, having just a customer Id doesn’t tell you the customer name unless you’ve memorized customer numbers with their corresponding names. The following code shows you how to show the customer name, which is located in a joined customers table:

select name, customerId, sum(quantity) as TotalQuantity from orders o join customers c on o.customerid = c.customerid where orderdate >= getdate() – 7

When you run the above query, you see three columns: name, customerid and TotalQuantity. This is a much more useful query, because you can see a quantity of products sold based on each customer. This can be added to any of your sales reports.

Other Aggregate Functions

SQL has several other aggregate functions. You can also count the number of records based on a specific query. For instance, suppose you want to find the number of orders placed within the last week. The following “count” SQL function determines the number of orders in total:

select count(*) as TotalOrders from orders where orderdate >= getdate() – 7

SQL also has the “avg” function. This function calculates the average, which is the sum of the specified column divided by the number of columns. For instance, you might want to know the average total for each order. This lets you calculate your average revenue and cost. The following SQL statement calculates the sales average:

select avg(order_total) as OrderTotal from orders where orderdate >= getdate() – 7

The above statement assumes that you store the order’s total in the “order_total” column. Notice you don’t need to do any calculations. Just use the avg function and SQL performs the basic calculation.

SQL also has a “min” and “max” function. The min function gets the lowest value in the record set and the max function gets the highest.

For instance, you might want to get the highest or lowest sale for the week. The following code gets the lowest (min) and highest (max) for the week:

select min(order_total) as LowestOrder from orders where orderdate >= getdate() – 7
select max(order_total) as LowestOrder from orders where orderdate >= getdate() – 7

Again, the where clause filters the data, so only the min and max order is pulled from the last 7 days.

Aggregate SQL functions make it easier for you to perform basic math on your columns. With some more complex queries, you can create reports and other data manipulations important for your business.

Learn more about SQL coding with a class at Udemy.com.