Counts and Totals

Intermediate SQL Server

Ginger Grant

Instructor

Examining Totals with Counts

SELECT COUNT(*) FROM Incidents

+-----------------+
|(No column name) |
+-----------------+
|6452             | 
+-----------------+

COUNT with DISTINCT

COUNT(DISTINCT COLUMN_NAME)

COUNT with DISTINCT in T-SQL (I)

SELECT COUNT(DISTINCT Country) AS Countries
FROM Incidents

+----------------+
|Countries       |
+-----------------+
|3               |
+-----------------+

COUNT with DISTINCT in T-SQL (II)

SELECT COUNT(DISTINCT Country) AS Countries,
COUNT(DISTINCT City) AS Cities 
FROM Incidents

+----------------+-------------+
|Countries       | Cities      |
+-----------------+-------------+
|3               | 3566        |
+-----------------+-------------+

COUNT AGGREGATION

GROUP BY can be used with COUNT() in the same way as the other aggregation functions such as AVG(), MIN(), MAX()
Use the ORDER BY command to sort the values
- ASC will return the smallest values first (default)
- DESC will return the largest values first

COUNT with GROUP BY in T-SQL

-- Count the rows, subtotaled by Country
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|5452                 |us               |
|750                  |NULL             |
|249                  |ca               |
|1                    |gb               |
+----------------------+-----------------+

COUNT with GROUP BY and ORDER BY in T-SQL (I)

-- Count the rows, subtotaled by Country
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country 
ORDER BY Country ASC

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|750                  |NULL             |
|249                  |ca               |
|1                    |gb               |
|5452                 |us               |
+----------------------+-----------------+

COUNT with GROUP BY and ORDER BY in T-SQL (II)

-- Count the rows, subtotaled by Country
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country 
ORDER BY Country DESC

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|5452                 |us               |
|1                    |gb               |
|249                  |ca               |
|750                  |NULL             |
+----------------------+-----------------+

Column totals with SUM

SUM() provides a numeric total of the values in a column
It follows the same pattern as other aggregations
Combine it with GROUP BY to get subtotals based on columns specified

Adding column values in T-SQL

-- Calculate the values subtotaled by Country
SELECT SUM(DurationSeconds) AS TotalDuration, Country
FROM Incidents
GROUP BY Country

+----------+--------------------+
|Country   |TotalDuration       |
+----------+--------------------+
|us        |17024946.750001565  |
|null      |18859192.800000012  |
|ca        |200975              |
|gb        |120                 |
+----------+--------------------+

Let's practice!

Intermediate SQL Server