Fisseha Berhane, PhD

Data Scientist

443-970-2353 fisseha@jhu.edu CV Resume Linkedin GitHub twitter twitter

MySQL Exercise 5: Summaries of Groups of Data

So far you've learned how to select, reformat, manipulate, order, and summarize data from a single table in database. In this lesson, you are going to learn how to summarize multiple subsets of your data in the same query. The method for doing this is to include a "GROUP BY" clause in your SQL query.

The GROUP BY clause

The GROUP BY clause comes after the WHERE clause, but before ORDER BY or LIMIT:

SELECT FROM WHERE ORDER BY

The GROUP BY clause is easy to incorporate into your queries. In fact, it might be a little too easy to incorporate into MySQL queries, because it can be used incorrectly in MySQL queries even when no error message is displayed. As a consequence, I suggest you adopt a healthy dose of caution every time you use the GROUP BY clause. By the end of this lesson, you will understand why. When used correctly, though, GROUP BY is one of the most useful and efficient parts of an SQL query, and once you are comfortable using it, you will use it very frequently.

To get started, load the SQL library and the Dognition database, and set the dognition database as the default:

In [1]:
%load_ext sql
%sql mysql://studentuser:studentpw@mysqlserver/dognitiondb
%sql USE dognitiondb
0 rows affected.
Out[1]:
[]

Let's return to a question from MySQL Exercise 4. How would you query the average rating for each of the 40 tests in the Reviews table? As we discussed, one very inefficient method to do that would be to write 40 separate queries with each one having a different test name in the WHERE conditional clause. Then you could copy or transcribe the results from all 40 queries into one place. But that wouldn't be very pleasant. Here's how you could do the same thing using one query that has a GROUP BY clause:

SELECT test_name, AVG(rating) AS AVG_Rating
FROM reviews
GROUP BY test_name

This query will output the average rating for each test. More technically, this query will instruct MySQL to average all the rows that have the same value in the test_name column.

Notice that I included test_name in the SELECT statement. As a strong rule of thumb, if you are grouping by a column, you should also include that column in the SELECT statement. If you don't do this, you won't know to which group each row of your output corresponds.

To see what I mean, try the query above without test_name included in the SELECT statement:

In [3]:
%%sql
SELECT test_name, AVG(rating) AS AVG_Rating
FROM reviews
GROUP BY test_name
40 rows affected.
Out[3]:
test_name AVG_Rating
1 vs 1 Game 3.9206
3 vs 1 Game 4.2857
5 vs 1 Game 3.9272
Arm Pointing 4.2153
Cover Your Eyes 2.6741
Delayed Cup Game 3.3514
Different Perspective 2.7647
Expression Game 4.0000
Eye Contact Game 2.9372
Eye Contact Warm-up 0.9632
Foot Pointing 4.0093
Impossible Task Game 3.0965
Impossible Task Warm-up 0.2174
Inferential Reasoning Game 4.5223
Inferential Reasoning Warm-up 4.3066
Memory versus Pointing 3.5584
Memory versus Smell 4.2623
Navigation Game 2.9841
Navigation Learning 2.0303
Navigation Warm-up 1.9805
Numerosity Warm-Up 2.6173
One Cup Warm-up 1.3693
Physical Reasoning Game 3.8492
Physical Reasoning Warm-up 1.6625
Self Control Game 3.8519
Shaker Game 4.6667
Shaker Warm-Up 2.1818
Shared Perspective 3.2778
Slide 4.5111
Smell Game 4.2857
Stair Game 4.2857
Switch 5.5676
Treat Warm-up 0.7909
Turn Your Back 3.1293
Two Cup Warm-up 1.6737
Warm-Up 1.2020
Watching 2.4594
Watching - Part 2 2.6570
Yawn Game 2.8477
Yawn Warm-up 2.0035

You can form groups using derived values as well as original columns. To illustrate this, let's address another question: how many tests were completed during each month of the year?

To answer this question, we need to take advantage of another datetime function described in the website below:

http://www.w3resource.com/mysql/date-and-time-functions/date-and-time-functions.php

MONTH() will return a number representing the month of a date entry. To get the total number of tests completed each month, you could put the MONTH function into the GROUP BY clause, in this case through an alias:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY Month;

You can also group by multiple columns or derived fields. If we wanted to determine the total number of each type of test completed each month, you could include both "test_name" and the derived "Month" field in the GROUP BY clause, separated by a comma.

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month;

MySQL allows you to use aliases in a GROUP BY clause, but some database systems do not. If you are using a database system that does NOT accept aliases in GROUP BY clauses, you can still group by derived fields, but you have to duplicate the calculation for the derived field in the GROUP BY clause in addition to including the derived field in the SELECT clause:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, MONTH(created_at);

Try the query once with test_name first in the GROUP BY list, and once with Month first in the GROUP BY list below. Inspect the outputs:

In [6]:
%%sql
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, MONTH(created_at)
LIMIT 20;
20 rows affected.
Out[6]:
test_name Month Num_Completed_Tests
1 vs 1 Game 1 25
1 vs 1 Game 2 28
1 vs 1 Game 3 22
1 vs 1 Game 4 12
1 vs 1 Game 5 13
1 vs 1 Game 6 18
1 vs 1 Game 7 36
1 vs 1 Game 8 17
1 vs 1 Game 9 28
1 vs 1 Game 10 27
1 vs 1 Game 11 15
1 vs 1 Game 12 14
3 vs 1 Game 1 35
3 vs 1 Game 2 28
3 vs 1 Game 3 34
3 vs 1 Game 4 16
3 vs 1 Game 5 34
3 vs 1 Game 6 42
3 vs 1 Game 7 37
3 vs 1 Game 8 23
In [7]:
%%sql
SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY  MONTH(created_at),test_name
LIMIT 20;
20 rows affected.
Out[7]:
test_name Month Num_Completed_Tests
1 vs 1 Game 1 25
3 vs 1 Game 1 35
5 vs 1 Game 1 59
Arm Pointing 1 622
Cover Your Eyes 1 452
Delayed Cup Game 1 356
Different Perspective 1 2
Expression Game 1 6
Eye Contact Game 1 624
Eye Contact Warm-up 1 707
Foot Pointing 1 506
Impossible Task Game 1 49
Impossible Task Warm-up 1 51
Inferential Reasoning Game 1 410
Inferential Reasoning Warm-up 1 425
Memory versus Pointing 1 464
Memory versus Smell 1 465
Navigation Game 1 57
Navigation Learning 1 59
Navigation Warm-up 1 62

Notice that in the first case, the first block of rows share the same test_name, but are broken up into separate months (for those of you who took the "Data Visualization and Communication with Tableau" course of this specialization, this is similar to what would happen if you put test_name first and created_at second on the rows or columns shelf in Tableau).

In the second case, the first block of rows share the same month, but are broken up into separate tests (this is similar to what would happen if you put created_at first and test_name second on the rows or columns shelf in Tableau). If you were to visualize these outputs, they would look like the charts below.

ORDER

Different database servers might default to ordering the outputs in a certain way, but you shouldn't rely on that being the case. To ensure the output is ordered in a way you intend, add an ORDER BY clause to your grouped query using the syntax you already know and have practiced:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY test_name, Month
ORDER BY test_name ASC, Month ASC;

Question 1: Output a table that calculates the number of distinct female and male dogs in each breed group of the Dogs table, sorted by the total number of dogs in descending order (the sex/breed_group pair with the greatest number of dogs should have 8466 unique Dog_Guids):

In [11]:
%%sql
SELECT breed_group, gender, COUNT(DISTINCT(dog_guid)) as Count
FROM dogs
GROUP BY breed_group,gender
ORDER BY Count DESC
LIMIT 5;
5 rows affected.
Out[11]:
breed_group gender Count
None male 8466
None female 8367
Sporting male 2584
Sporting female 2262
Herding male 1736

Some database servers, including MySQL, allow you to use numbers in place of field names in the GROUP BY or ORDER BY fields to reduce the overall length of the queries. I tend to avoid this abbreviated method of writing queries because I find it challenging to troubleshoot when you are writing complicated queries with many fields, but it does allow you to write queries faster. To use this method, assign each field in your SELECT statement a number acording to the order the field appears in the SELECT statement. In the following statement:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests

test_name would be #1, Month would be #2, and Num_Completed_Tests would be #3. You could then rewrite the query above to read:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;

Question 2: Revise the query your wrote in Question 1 so that it uses only numbers in the GROUP BY and ORDER BY fields.

In [12]:
%%sql
SELECT breed_group, gender, COUNT(DISTINCT(dog_guid)) as Count
FROM dogs
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 5;
5 rows affected.
Out[12]:
breed_group gender Count
None male 8466
None female 8367
Sporting male 2584
Sporting female 2262
Herding male 1736

The HAVING clause

Just like you can query subsets of rows using the WHERE clause, you can query subsets of aggregated groups using the HAVING clause. However, wheras the expression that follows a WHERE clause has to be applicable to each row of data in a column, the expression that follows a HAVING clause has to be applicable or computable using a group of data.

If you wanted to examine the number of tests completed only during the winter holiday months of November and December, you would need to use a WHERE clause, because the month a test was completed in is recorded in each row. Your query might look like this:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
ORDER BY 3 DESC;

If you then wanted to output only the test-month pairs that had at least 20 records in them, you would add a HAVING clause, because the stipulation of at least 20 records only makes sense and is only computable at the aggregated group level:

SELECT test_name, MONTH(created_at) AS Month, COUNT(created_at) AS Num_Completed_Tests
FROM complete_tests
WHERE MONTH(created_at)=11 OR MONTH(created_at)=12
GROUP BY 1, 2
HAVING COUNT(created_at)>=20
ORDER BY 3 DESC;

Question 3: Revise the query your wrote in Question 2 so that it (1) excludes the NULL and empty string entries in the breed_group field, and (2) excludes any groups that don't have at least 1,000 distinct Dog_Guids in them. Your result should contain 8 rows. (HINT: sometimes empty strings are registered as non-NULL values. You might want to include the following line somewhere in your query to exclude these values as well):

breed_group!=""
In [16]:
%%sql
SELECT breed_group, gender, COUNT(DISTINCT(dog_guid)) as Count
FROM dogs
WHERE  breed_group IS NOT NULL OR breed_group!="" OR breed_group!="None"
GROUP BY 1,2
HAVING COUNT(DISTINCT(dog_guid)) >= 1000
ORDER BY 3 DESC
8 rows affected.
Out[16]:
breed_group gender Count
Sporting male 2584
Sporting female 2262
Herding male 1736
Herding female 1704
Toy male 1473
Toy female 1145
Non-Sporting male 1098
Working male 1075

We will review several issues that can be tricky about using GROUP BY in your queries in the next lesson, but those issues will make more sense once you are sure you are comfortable with the basic functionality of the GROUP BY and HAVING clauses.

Practice incorporating GROUP BY and HAVING into your own queries.

Question 4: Write a query that outputs the average number of tests completed and average mean inter-test-interval for every breed type, sorted by the average number of completed tests in descending order (popular hybrid should be the first row in your output).

In [21]:
%%sql
SELECT breed_type, AVG(total_tests_completed) as Tests_completed, AVG(mean_iti_minutes)
FROM dogs
GROUP BY breed_type
ORDER BY AVG(total_tests_completed) DESC
LIMIT 10;
4 rows affected.
Out[21]:
breed_type Tests_completed AVG(mean_iti_minutes)
Popular Hybrid 10.257530120481928 2834.3205728931534
Cross Breed 9.945900537634408 2872.351156110182
Pure Breed 9.871602824737856 3193.350493795222
Mixed Breed/ Other/ I Don't Know 9.54250850170034 3023.0711302156274

Question 5: Write a query that outputs the average amount of time it took customers to complete each type of test where any individual reaction times over 6000 hours are excluded and only average reaction times that are greater than 0 are included (your output should end up with 58 rows).

In [46]:
%%sql
SELECT test_name, AVG(TIMESTAMPDIFF(HOUR,start_time,end_time))AS AvGDuration
FROM exam_answers
WHERE TIMESTAMPDIFF(HOUR,start_time,end_time) <6000
GROUP BY test_name
HAVING AVG(TIMESTAMPDIFF(SECOND,start_time,end_time))>0 
ORDER BY AvGDuration DESC;
67 rows affected.
Out[46]:
test_name AvGDuration
Social-Quiz 81.9923
Diet 32.7712
Sociability 21.1726
Activity 20.0071
Yawn Warm-up 15.2669
Shy/Boldness 11.6170
Watching 11.4743
Attachment 11.0557
Excitability 10.9972
Surprise And Delight 10.9355
Confinement 9.2003
Set 3 7.0007
Navigation Warm-up 6.1733
Gender 6.1326
Physical 5.6950
Owner 5.2280
Emotions 4.6607
Numerosity Warm-Up 4.5937
Yawn Game 3.8349
Recall 3.8206
Perception 3.8185
Impossible Task Warm-up 3.8022
Puzzles 3.7676
Partnership 3.4735
Obedience 3.4643
Purina-Only 3.3696
Set 1 3.0899
Social 2.7286
Training 2.6089
Purina 2.4655
Treat Warm-up 2.2185
Toys 2.2110
Watching - Part 2 2.1875
Warm-Up 2.0840
Delayed Cup Game 1.4267
One Cup Warm-up 1.4191
Inferential Reasoning Warm-up 1.3832
Eye Contact Warm-up 1.2267
Memory versus Smell 1.1865
Smell Game 1.1813
Eye Contact Game 1.1389
Cover Your Eyes 0.8247
Turn Your Back 0.7646
Arm Pointing 0.6092
Foot Pointing 0.5739
Slide 0.5362
Environment 0.4971
Physical Reasoning Warm-up 0.4921
Inferential Reasoning Game 0.3883
Stair Game 0.3021
Navigation Learning 0.2271
Memory versus Pointing 0.2205
Impossible Task Game 0.1913
Navigation Game 0.1273
Set 2 0.1259
Two Cup Warm-up 0.1223
Physical Reasoning Game 0.0819
Shaker Warm-Up 0.0644
Switch 0.0531
Different Perspective 0.0493
3 vs 1 Game 0.0000
5 vs 1 Game 0.0000
1 vs 1 Game 0.0000
Self Control Game 0.0000
Shared Perspective 0.0000
Expression Game 0.0000
Shaker Game 0.0000

Question 6: Write a query that outputs the total number of unique User_Guids in each combination of State and ZIP code (postal code) in the United States, sorted first by state name in ascending alphabetical order, and second by total number of unique User_Guids in descending order (your first state should be AE and there should be 5043 rows in total in your output).

In [58]:
%%sql
SELECT state, zip, COUNT(DISTINCT(user_guid)) AS Total_Users
FROM users
WHERE Country="US"
GROUP BY state, zip
ORDER BY 1 ASC, 3 DESC
LIMIT 10;
10 rows affected.
Out[58]:
state zip Total_Users
AE 9128 2
AE 9053 1
AE 9107 1
AE 9469 1
AE 9845 1
AK 99507 3
AK 99709 3
AK 99501 2
AK 99577 2
AK 99824 1

Question 7: Write a query that outputs the total number of unique User_Guids in each combination of State and ZIP code in the United States that have at least 5 users, sorted first by state name in ascending alphabetical order, and second by total number of unique User_Guids in descending order (your first state/ZIP code combination should be AZ/86303).

In [59]:
%%sql
SELECT state, zip, COUNT(DISTINCT(user_guid)) AS Total_Users
FROM users
WHERE Country="US"
GROUP BY state, zip
HAVING Total_Users >=5
ORDER BY 1 ASC, 3 DESC
LIMIT 10;
10 rows affected.
Out[59]:
state zip Total_Users
AZ 86303 14
AZ 85718 6
AZ 85749 5
AZ 85253 5
AZ 85254 5
AZ 85260 5
AZ 85711 5
CA 92107 16
CA 90046 13
CA 92130 12




comments powered by Disqus