COUNT and SUM very frequently.

COUNT is the only aggregate function that can work on any type of variable. The other four aggregate functions are only appropriate for numerical data.

All aggregate functions require you to enter either a column name or a “*” in the parentheses after the function word.

1. The COUNT function

First, load the sql library and the Dognition database, and set dognition as the default database.

Run SELECT function on the breed of dogs in the Dognition database. This is to find out the number of rows, (which when using jupyter, you can see right above the output)

SELECT breed
FROM dogs

Jupyter tells that 35050 rows are “affected”, meaning there are 35050 rows in the output of the query

Now lets apply the COUNT() function:

Applying COUNT gives us 35050. COUNT gives the numeric number of rows without  displaying the actual rows of data that are being aggregated.

You can use DISTINCT with COUNT to count all the unique values in a column,

SELECT COUNT(DISTINCT breed)
  FROM dogs

Find out how many individual dogs successfully completed at least one test?

SELECT COUNT(DISTINCT Dog_Guid)
  FROM complete_tests

Question 1: Try combining this query with a WHERE clause to find how many individual dogs completed tests after March 1, 2014 (the answer should be 13,289):

Looking the columns in the complete_tests table first

Question 3: Now count the number of rows in the exclude column of the dogs table:

Note:

When a column is included in a count function, null values are ignored in the count. When an asterisk is included in a count function, nulls are included in the count.

Question 4: How many distinct dogs have an exclude flag in the dogs table (value will be “1”)? (the answer should be 853)

2. The SUM Function

The fact that the output of:

SELECT COUNT(exclude) 
FROM dogs

was so much lower than:

SELECT COUNT(*)
FROM dogs

suggests that there must be many NULL values in the exclude column. There are several ways to deal with this.

SELECT SUM(ISNULL(exclude))
FROM dogs

Try counting the number of NULL values in the exclude column:

The output should return a value of 34,025. When you add that number to the 1025 entries that have an exclude flag, you get a total of 35,050, which is the number of rows reported by SELECT COUNT(*) from dogs.

3. The AVG, MIN, and MAX Functions

AVG, MIN, and MAX all work very similarly to SUM.

Retrieving the average, minimum, and maximum rating customers gave to this question after completing the “Eye Contact Game” :

More practice questions

Question 6: How would you query how much time it took to complete each test provided in the exam_answers table, in minutes? Title the column that represents this data “Duration.”  (HINT: use the TIMESTAMPDIFF function described at: http://www.w3resource.com/mysql/date-and-time-functions/date-and-time-functions.php.

Question 7: Include a column for Dog_Guid, start_time, and end_time in your query, and examine the output. Do you notice anything strange?

Observation: some of your calculated durations appear to be “0.”

Question 8: What is the average amount of time it took customers to complete all of the tests in the exam_answers table, if you do not exclude any data (the answer will be approximately 587 minutes)?

Question 9: What is the average amount of time it took customers to complete the “Treat Warm-Up” test, according to the exam_answers table (about 165 minutes, if no data is excluded)?

Question 10: How many possible test names are there in the exam_answers table?

Question 11: What is the minimum and maximum value in the Duration column of your query that included the data from the entire table?

The minimum Duration value is negative! The end_times entered in rows with negative Duration values are earlier than the start_times. Unless Dognition has created a time machine, that’s impossible and these entries must be mistakes.

Question 12: How many of these negative Duration entries are there? (the answer should be 620)

Question 13: How would you query all the columns of all the rows that have negative durations so that you could examine whether they share any features that might give you clues about what caused the entry mistake?