Conteggi e totali

SQL Server intermedio

Ginger Grant

Instructor

Esaminare i totali con COUNT

SELECT COUNT(*) FROM Incidents

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

COUNT con DISTINCT

COUNT(DISTINCT COLUMN_NAME)

COUNT con DISTINCT in T-SQL (I)

SELECT COUNT(DISTINCT Country) AS Countries
FROM Incidents

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

COUNT con DISTINCT in T-SQL (II)

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

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

Aggregazione COUNT

GROUP BY si usa con COUNT() come con altre funzioni di aggregazione (AVG(), MIN(), MAX())
Usa ORDER BY per ordinare i valori
- ASC restituisce prima i valori minori (predefinito)
- DESC restituisce prima i valori maggiori

COUNT con GROUP BY in T-SQL

-- Conta le righe, con subtotale per Country
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country

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

COUNT con GROUP BY e ORDER BY in T-SQL (I)

-- Conta le righe, con subtotale 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 con GROUP BY e ORDER BY in T-SQL (II)

-- Conta le righe, con subtotale 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             |
+----------------------+-----------------+

Totali di colonna con SUM

SUM() fornisce il totale numerico dei valori in una colonna
Segue lo stesso schema delle altre aggregazioni
Combinala con GROUP BY per ottenere subtotali in base alle colonne indicate

Sommare i valori di colonna in T-SQL

-- Calcola i valori con subtotale 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                 |
+----------+--------------------+

Esercitiamoci!

SQL Server intermedio