For circumstances where you want your aggregate functions to operate on distinct values, learn how SQL provides the DISTINCT keyword to fold duplicates before aggregation.
- [Instructor] For circumstances…where you want your aggregate function…to operate on distinct values,…SQL allows the distinct keyword to be used…in aggregate functions.…Let's see how this works.…Go ahead and select the world database for this.…And now if I were to count…the head of state column in the country database,…you notice that we get a value of 238,…which means that there's 238 non-null values…in the head of state column.…
This is not really counting the heads of state,…it's counting the rows that have a value…in the head of state column.…If you just look at the column,…and I'll…take the count function out and we'll order it…by head of state.…You notice something interesting here.…That some of these heads of state…are head of state of many countries.…You'll also notice that the database is very old,…and that's fine, for our purposes.…
But if we go back to our count,…we can count head of state…but we can count just the distinct values,…not all of the values.…So if I say distinct...…Now we get 179.…This is very different than the 238 we had before.…
Author
Bill Weinman
Released
7/2/2018- Understanding SQL terminology and syntax
- Creating new tables
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Understanding numeric types
- Using aggregate functions and transactions
- Automating data with triggers
- Creating views
- Defining functions in PHP
Skill Level Beginner
Duration
Views
-
Introduction
-
Welcome1m 16s
-
Exercise files2m 21s
-
Course overview1m 38s
-
-
1. Installation
-
Installing on Windows: Part II10m 16s
-
Installing on a Mac: Part I6m 45s
-
Installing on a Mac: Part II12m 10s
-
2. SQL Overview
-
About the overview3m 39s
-
Database organization2m 54s
-
The SELECT statement5m 49s
-
Selecting rows2m 3s
-
Selecting columns1m 29s
-
Counting rows2m 42s
-
Inserting data3m 9s
-
Updating data2m 28s
-
Deleting data1m 42s
-
-
3. Fundamental Concepts
-
Creating a table3m 40s
-
Deleting a table2m 14s
-
Inserting rows3m 31s
-
Deleting rows1m 55s
-
The NULL value5m 22s
-
Constraining columns3m 17s
-
Changing a schema2m 3s
-
ID columns2m 21s
-
Filtering data5m 43s
-
Removing duplicates2m 37s
-
Ordering output2m 23s
-
Conditional expressions2m 30s
-
-
4. Relationships
-
Understanding JOIN2m 19s
-
Accessing related tables4m 31s
-
Relating multiple tables4m 27s
-
-
5. Strings
-
About SQL strings1m 33s
-
Removing spaces2m 26s
-
Folding case2m 13s
-
-
6. Numbers
-
Numeric types3m 32s
-
What type is that value?2m 12s
-
Integer division1m 56s
-
Rounding numbers1m 2s
-
-
7. Dates and Times
-
Dates and times1m 52s
-
-
8. Aggregates
-
What are aggregates?4m 38s
-
Using aggregate functions1m 57s
-
Aggregating DISTINCT values1m 53s
-
-
9. Transactions
-
What are transactions?2m 2s
-
Data integrity3m 10s
-
Performance3m 27s
-
-
10. Triggers
-
Preventing updates2m 36s
-
Example: Timestamps2m 26s
-
11. Views and Subselects
-
Creating a subselect3m 47s
-
Creating a view3m 32s
-
Creating a joined view2m 32s
-
-
12. Defined Functions
-
Overview1m 7s
-
Defining functions in PHP4m 27s
-
-
13. A Simple CRUD Application
-
Embedding SQL4m 50s
-
The SELECT functions3m 30s
-
-
Conclusion
-
Goodbye1m 3s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Aggregating DISTINCT values