Working with Aggregates

Question 1

What’s an aggregate function?

Answer 1

These are functions that perform a calculation on a set of values and return a single value. Examples include SUM, COUNT, AVG (average), MIN (minimum), and MAX (maximum).

Question 2

In databases, when you have a lot of data and you want to summarize it or get some statistics (like totals, averages, counts, etc.) for groups of related data, what would you use?

Answer 2

Aggregates and Grouping

Question 3

List everything you need to know about Aggregates and Grouping

Answer 3

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

GROUP BY comes after the WHERE clause (if there is one) and before the ORDER BY clause

Items in the SELECT statement that aren’t aggregated must be included in the GROUP BY clause

Multiple aggregate functions can be applied to a single query

Different columns can be aggregated in the same query

If you use an ORDER BY clause the column(s) must be included in the GROUP BY clause

Question 4

What does the count() function do?

Answer 4

The COUNT function counts the total occurrences of the specified column in a record set

Question 4

What happens when you use an asterisk with the count function?

Answer 4

The function counts the total number of rows

Question 5

List what you know about the SUM() function.

Answer 5

The SUM function adds together the numbers in a grouping

You insert the DISTINCT keyword if you want to only add
distinct values

SUM will add both positive and negative values

Question 6

List what you know about the AVG() function

Answer 6

The AVG function returns the average of
values in a group

Null values are ignored

Question 6

List what you know about the Min() and Max() function

Answer 6

The MIN function returns the minimum value in a column

The MAX function returns the maximum
value in a column

Both functions can work on string as well as numeric values

Question 7

When can you aggregate data without the GROUP BY function?

Answer 7

The GROUP BY clause is not used if you are grouping
against an entire result set or table

If there are no non-aggregated columns then you don’t need the GROUP BY clause

Question 8

List what you know about Aggregates and NULL

Answer 8

List what you know about the aggregate functions will
exclude NULL values from their calculations

If you want to include NULL values then you will need to enclose the column in an ISNULL scalar function

Question 9

List what you