Functions for Manipulating Data in SQL Server
Ana Voicu
Data Engineer
COUNT([ALL] expression)
COUNT(DISTINCT expression)
COUNT(*)
SELECT
COUNT(ALL country) AS total_countries,
COUNT(country) AS total_countries,
COUNT(DISTINCT country) AS distinct_countries,
COUNT(*) AS all_voters
FROM voters;
| count_countries_all | count_countries | distinct_countries | all_voters |
|---------------------|-----------------|--------------------|------------|
| 196 | 196 | 11 | 196 |
SUM([ALL] expression)
SUM(DISTINCT expression)
SELECT
first_name,
last_name,
total_votes
FROM voters
WHERE total_votes = 153;
| first_name | last_name | total_votes |
|------------|-----------|-------------|
| Isabella | Roberts | 153 |
| Chase | Ward | 153 |
| Kendra | Ortega | 153 |
| Bruce | Moreno | 153 |
SELECT
SUM(ALL total_votes) AS tot_votes1,
SUM(total_votes) AS tot_votes2,
SUM(DISTINCT total_votes) AS dist
FROM voters
WHERE total_votes = 153;
| tot_votes1 | tot_votes2 | tot_dis_votes |
|-------------|-------------|----------------|
| 612 | 612 | 153 |
MAX([ALL] expression)
MAX(DISTINCT expression)
MIN([ALL] expression)
MIN(DISTINCT expression)
SELECT
MIN(rating) AS min_rating,
MAX(rating) AS max_rating
FROM ratings;
| min_rating |max_rating |
|------------|-----------|
| 1.0000 | 5.0000 |
AVG([ALL] expression)
AVG(DISTINCT expression)
SELECT
AVG(rating) AS avg_rating,
AVG(DISTINCT rating) AS avg_dist
FROM ratings;
| avg_rating |avg_dist |
|------------|---------|
| 3.184665 | 2.788461|
SELECT company,
AVG(rating) AS avg_rating
FROM ratings
GROUP BY company;
| company |avg_rating |
|------------|-----------|
| A. Morin | 3.250000 |
| Acalli | 3.500000 |
| Adi | 3.000000 |...
Functions for Manipulating Data in SQL Server