Aggregates and Joins
Learn about the aggregate functions and joins.
Description of tables
The tables, “Sales.ProductSales”, “Prod.Products” and “Dept.JobCandidate” shown below are used in code examples. The “ProductSales” table is in the “Sales” schema, which has seven columns and six rows, as illustrated below:
Sales.ProductSales Table
TransactionID | ProductID | UnitPrice | Quantity | CustomerID | SuptPkgID | SuptPkgCost |
1 | 1 | 1000.00 | 1 | 1 | 1 | 0.00 |
2 | 1 | 1000.00 | 1 | 2 | 1 | 0.00 |
3 | 2 | 1500.00 | 2 | 3 | 2 | 250.00 |
4 | 3 | 750.00 | 1 | 4 | 2 | 250.00 |
5 | 4 | 2500.00 | 1 | 5 | 2 | 250.00 |
6 | 1 | 1000.00 | 1 | 5 | 2 | 250.00 |
The “Products” table is in the “Prod” schema, which has seven columns and four rows, as illustrated below:
Prod.Products Table
ProductID | ModVersion | Color | ModuleName | ModDescr | DatabaseIncl | UnitPrice |
1 | 1.0 | Red | Module A | Interface for using GIS maps | Yes | 1000.00 |
2 | 1.0 | Blue | Module B | Database system that allows them to enter spatial data | Yes | 1500.00 |
3 | 1.0 | Green | Module C | Email application that sends emails based on spatial preferences | No | 750.00 |
4 | 1.0 | Red | Module D | Hosting service for all modules | Yes | 2500.00 |
The “Job_Candidate” table is in the “Sales” schema, which has six columns and three rows, as illustrated below:
Dept.JobCandidate Table
CandidateID | FirstName | LastName | Resume | PID | |
1 | John | Smith | jsmith@company.com | NULL | 001 |
2 | Debbie | Anderson | danderson@company.com | Available | 002 |
3 | Jane | Hopper | jane.Hopper@gmail.com | Not Available | 009 |
The “Customer” table is in the “Cust” schema, which has three columns and five rows, as illustrated below:
CustomerID | CustomerName | FtpAddress |
1 | GeoStudy,Inc | ftp.geostudy.com |
2 | Weather Watchers | stfp.weatherinfo.com |
3 | GIS In Motion | ftp.gismotion |
4 | GIS Tracker | sftp.gistracking |
5 | Earth Mapping | sftp.emap.com |
Aggregates
Aggregate functions are ways to transform and calculate data into a single result set. There are various types of aggregate functions.
Here’s a list of the most common aggregate functions:
MAX: It provides the highest value of all values.AVG: It provides the average of all values.COUNT: It provides a count of how many records are in a table or column, but this function ignores any NULL values if counting in one specific column. Though it is still an aggregate function, it does not perform a calculation on the values in a group.SUM: It provides a total of all values combined.MIN: It provides the lowest value of all values.
The following command selects the record that contains the highest sales price:
SELECT MAX(UnitPrice) AS HighestPriceFROM Sales.ProductSales
This outputs the highest value in a column named HighestPrice .
Output of MAX()
Results | |
HighestPrice | |
1 | 2500 |
The following command calculates the average quantity based on per product order. This also groups it by the ProductID in line 4 and sorts it in descending order by the same column in line 5.
SELECT ProductID,AVG(Quantity) AS AvgQuantityFROM Sales.Product_SalesGROUP BY ProductIDORDER BY ProductID DESC
It shows the following output:
Output of AVG()
Results | ||
ProductID | AvgQuantity | |
1 | 4 | 1 |
2 | 3 | 1 |
3 | 2 | 1 |
4 | 1 | 1 |