Aantallen en totalen

SQL Server voor gevorderden

Ginger Grant

Instructor

Totalen bekijken met COUNT

SELECT COUNT(*) FROM Incidents

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

COUNT met DISTINCT

COUNT(DISTINCT COLUMN_NAME)

COUNT met DISTINCT in T-SQL (I)

SELECT COUNT(DISTINCT Country) AS Countries
FROM Incidents

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

COUNT met DISTINCT in T-SQL (II)

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

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

COUNT-aggregatie

GROUP BY kun je met COUNT() gebruiken zoals met andere aggregaties als AVG(), MIN(), MAX()
Gebruik ORDER BY om te sorteren
- ASC geeft eerst de kleinste waarden (standaard)
- DESC geeft eerst de grootste waarden

COUNT met GROUP BY in T-SQL

-- Tel de rijen, uitgesplitst per Country
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country

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

COUNT met GROUP BY en ORDER BY in T-SQL (I)

-- Tel de rijen, uitgesplitst per 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 met GROUP BY en ORDER BY in T-SQL (II)

-- Tel de rijen, uitgesplitst per 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             |
+----------------------+-----------------+

Kolomsommen met SUM

SUM() geeft de numerieke som van de waarden in een kolom
Volgt hetzelfde patroon als andere aggregaties
Combineer met GROUP BY voor subtotalen per opgegeven kolommen

Kolomwaarden optellen in T-SQL

-- Bereken waarden, uitgesplitst per Country
SELECT SUM(DurationSeconds) AS TotalDuration, Country
FROM Incidents
GROUP BY Country

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

Laten we oefenen!

SQL Server voor gevorderden