Join Dan Sullivan for an in-depth discussion in this video Grouping and totaling, part of Advanced SQL for Data Scientists.
- [Instructor] Let's use the view we just created…to get a count of the number of employees…in each region.…So we'll enter a SELECT query…and let's select the company region…and let's get a count of company regions.…And we'll get this from our view,…staff_div_reg, now we're aggregating,…we're using a count, so we'll have to…have a GROUP BY statement,…and in this case we'll want to group…by company region.…Let's add an ORDER BY so we get the company region…counts listed alphabetically.…And I'll execute, and we'll notice we have…our list of seven regions, with a count…of the number of staff in each.…
Now if we want counts by both region…and division, we can use a feature…called grouping sets.…Here, let's look at an example…that returns employee counts…by division and by region.…So the first thing I'll do is I will add…company division to my list of columns that I'm returning.…In the GROUP BY clause, I'm going to…add the phrase, grouping sets,…and then I'm going to give it a list…of columns that I would like to group by.…
The course begins with a brief overview of SQL. Then the five major topics a data scientist should understand when working with relational databases: basic statistics in SQL, data preparation in SQL, advanced filtering and data aggregation, window functions, and preparing data for use with analytics tools.
- Data manipulation
- ANSI standards
- SQL and variations
- Statistical functions in SQL
- String, numeric, and regular expression functions in SQL
- Advanced filtering techniques
- Advanced aggregation techniques
- Windowing functions for working with ordered data sets
Skill Level Advanced
1. SQL as a Tool for Data Science
SQL data definition features5m 32s
2. Basic Statistics with SQL
3. Data Munging with SQL
4. Filtering, Joins, and Aggregation
5. Window Functions and Ordered Data
6. Preparing Data for Analytics Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press 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.