{"id":147,"date":"2014-02-21T15:30:43","date_gmt":"2014-02-21T22:30:43","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=147"},"modified":"2016-10-21T13:37:06","modified_gmt":"2016-10-21T20:37:06","slug":"aggregate-queries","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/aggregate-queries\/","title":{"rendered":"Aggregation Queries in SQL"},"content":{"rendered":"

In SQL you can generate statistical summaries of the records in a query using aggregation functions and clauses. In many cases, you will need to do some processing and transformations to your data before summarizing it.<\/p>\n

Aggregation Functions<\/h2>\n

These functions operate on the values of a column (x) and return a value as shown in this table:<\/p>\n\n\n\n\n\n\n\n\n\n
Aggregation function<\/td>\nReturns<\/td>\n<\/tr>\n
MIN(x)<\/td>\nminimum value in column x<\/td>\n<\/tr>\n
MAX(x)<\/td>\nmaximum value in column x<\/td>\n<\/tr>\n
SUM(x)<\/td>\nsum of values in column x<\/td>\n<\/tr>\n
AVG(x)<\/td>\naverage value in column x<\/td>\n<\/tr>\n
STDEV(x)<\/td>\nstandard deviation of values in column x<\/td>\n<\/tr>\n
COUNT(x)<\/td>\nthe number of values in column x<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The following queries will return a single value as result of the aggregation function:<\/p>\n

SELECT AVG<\/span> (FinalGrade)
\nFROM Registrations
\nWHERE CourseID = \u201cBAMS 580\u201d<\/p>\n

SELECT COUNT<\/span> (StudentID)
\nFROM Students
\nWHERE Program = \u201cMMOR\u201d<\/p>\n

COUNT() Function<\/strong><\/p>\n

Depending on what information you are looking for, the Count function can be used in three different ways:<\/p>\n\n\n\n\n\n
COUNT(x)<\/td>\nReturns the number of non-null values in column x.<\/td>\n<\/tr>\n
COUNT(*)<\/td>\nReturns the number of rows in the queried table.<\/td>\n<\/tr>\n
COUNT(DISTINCT x)<\/td>\nReturns the number of distinct values in column x.
\nNote: this function is not available in MS Access<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This query will return the same value as the previous example (assuming the students table has no entries with null ID):<\/p>\n

SELECT COUNT (*)<\/span>
\nFROM Students
\nWHERE Program = \u201cMMOR\u201d<\/p>\n

Aggregation Clauses<\/h2>\n

The GROUP BY<\/span> clause divides rows into groups and apply aggregation operations to each group. For example, this query will return the average grade for each course and year:<\/p>\n

SELECT CourseID, Year, AVG (FinalGrade)
\nFROM Registrations
\nGROUP BY CourseID, Year<\/p>\n

The HAVING<\/span> clause specifies a condition for groups displayed. This query will display the number of students only for courses after 2010.<\/p>\n

SELECT CourseID, Year, COUNT (StudentID)
\nFROM Registrations
\nGROUP BY CourseID, Year
\nHAVING Year > 2010<\/p>\n

Aggregation functions can be included in the having clause:<\/p>\n

SELECT CourseID, Year, COUNT (StudentID)
\nFROM Registrations
\nGROUP BY CourseID, Year
\nHAVING\u00a0COUNT (StudentID) > 10<\/p>\n

Functions for data transformation<\/h2>\n

When summarizing in SQL you might need to do some conversions to the data you want to display. The IIF function<\/a> returns one of two values depending on a logical condition.<\/p>\n

SELECT StudentName, IIF<\/span>( Program=’MMOR’, 1, 0) as MMORStudent<\/span>
\nFROM Students<\/p>\n

When summarizing it can be useful to transform categorical variables into dummy variables that can then be aggregated using math functions:<\/p>\n

SELECT AVG<\/span>( IIF<\/span>( FinalGrade<50, 1, 0)) as Total_Students_Failed_BAMS580<\/span>
\nFROM Registrations
\nWHERE CourseID=’BAMS580′<\/p>\n

This webpage<\/a> shows other examples of conversions you can do. Keep in mind that IIF function works only for Microsoft databases since 2012. A more portable function across SQL platforms is CASE expression<\/a> (except for MS Access).<\/p>\n

Another way to summarize data in SQL is the TRANSFORM statement<\/a>, which allows to display values from specified fields or expressions as column headings.<\/p>\n","protected":false},"excerpt":{"rendered":"

In SQL you can generate statistical summaries of the records in a query using aggregation functions and clauses. In many cases, you will need to do some processing and transformations to your data before summarizing it. Aggregation Functions These functions operate on the values of a column (x) and return a value as shown in […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":74,"menu_order":7,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-147","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":30,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/147\/revisions"}],"predecessor-version":[{"id":1036,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/147\/revisions\/1036"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/74"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}