1. Counts and Totals
This lesson expands upon the aggregations that we covered in the first chapter by looking at two different functions, SUM and COUNT. These functions can be used to get the sum of all values of a column and count the number of records in a table or a query, respectively.
2. Examining Totals with Counts
Using the COUNT function, you can obtain the total number of records in a table. This is a quick way of determining how big the dataset really is. You can pass the asterik to COUNT to indicate that you want to count the number of records in the entire table.
3. COUNT with DISTINCT
If you want to know the number of unique values in a column, you can use the keyword DISTINCT. This is not the same as the number of rows in the column unless every value in the column listed is unique.
Let's look at an example using the Incidents table from the previous chapter.
4. COUNT with DISTINCT in T-SQL (I)
Here we pass DISTINCT Country to COUNT to count the number of unique countries in the Incidents table. Note that you can do this for more than one column.
5. COUNT with DISTINCT in T-SQL (II)
Here we calculate the unique values in both the Country and City columns by placing the keyword distinct inside the parenthesis after the Keyword Count.
6. COUNT AGGREGATION
As we previously saw with MIN, AVG and MAX, you can use COUNT combined with GROUP BY to provide counts of groups of rows. To sort the result, you can use ORDER BY. You can use the ASC keyword to sort the rows in ascending order, which is the default. To sort the rows in descending order, use the keyword DESC.
7. COUNT with GROUP BY in T-SQL
Here we count the total number of rows for each country using GROUP BY and the data is not sorted
8. COUNT with GROUP BY and ORDER BY in T-SQL (I)
To sort the values in ascending order of Country, you can use ORDER BY followed by the word Country. The keyword ASC is optional since sorting in the ascending order is default.
9. COUNT with GROUP BY and ORDER BY in T-SQL (II)
To sort the values in descending order of Country, use the DESC keyword in ORDER BY. This way, US shows first
10. Column totals with SUM
Similar to other aggregation functions, you can use the SUM function to calculate the sum of all values in a column.
11. Adding column values in T-SQL
Here we add values in the DurationSeconds column using the SUM function for each unique value in Country using GROUP BY.
12. Let's practice!
Now let's try some examples.