Databases · Lesson 7.1
One number from millions of rows
So LIMIT 3 sent back just three rows: Dee, Ada and Fay.
And SELECT ID FROM users sends back an ID for every learner: 2,000,000 rows.
Now say Simplified's analytics screen shows how many learners have signed up.
That's just one number: 2,000,000.
But must Simplified's server fetch 2,000,000 rows, just to show one number?
No: it can ask Postgres to do the counting, and send back only the answer.
Where the column names go, it writes COUNT star.
COUNT star counts the rows that pass, and sends back that one number.
The star means it counts whole rows, whatever is in them.
Postgres still reads users one row at a time.
At Ada's row, it counts 1.
At Ben's, 2.
At Cy's, 3, even with 0 XP.
And so on, all the way to the last learner: 2,000,000.
Then it sends back one row, holding 2,000,000.
So the server fetches just that one row, and shows 2,000,000.
Say Instagram keeps your followers in a table, one row for each follower.
To show your follower count, it might send SELECT COUNT star FROM followers.
Postgres counts each follower's row: 1, 2, 3, 4, and on to the last.
It sends back one row, holding 312: the number above Followers.
Search Amazon for headphones, and it might count the products that match.
Its WHERE keeps only the products with headphones in their name, capital H or not.
Then COUNT star counts the rows that pass: 1, 2, 3, and on.
It sends back 1,204: the number of results Amazon shows.
And if no product matched, it would still send back one row, holding 0.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT COUNT(*) FROM users WHERE xp = 40;.
What number comes back in the result?
Explanation
First, xp = 40 passes only the rows whose xp is exactly 40: Ben's and Eli's.
Then, COUNT(*) counts the rows that pass: there are 2.
Therefore, the result is one row holding 2.
Say a YouTube channel keeps its 5 videos in a table, with a views column. They have 12,400, 830,000, 96,000, 410,000 and 3,100 views. Its database gets SELECT COUNT(*) FROM videos WHERE views > 1000000;.
What number comes back?
Databases · Lesson 7.2
Adding up a column
So SELECT COUNT star FROM users counted every learner, and sent back one number.
Now say the analytics screen shows the XP of all its learners, together.
But COUNT star only counts rows; it never looks at the XP in them.
So how does Postgres add up a column, instead of counting rows?
Where the column names go, it writes SUM of XP.
SUM adds up a column's values, over the rows that pass.
So the query says SELECT SUM of XP FROM users.
Say users held only the six learners you've met, Ada to Fay.
Postgres reads users one row at a time, keeping a running total.
Ada's 120 starts the total at 120.
Ben's 40 makes 160. Cy's 0 leaves it at 160.
Dee's 300 makes 460.
Eli's 40 makes 500.
And Fay's 75 makes 575.
Then it sends back one row, holding the total: 575.
Say DoorDash keeps your order in a table: a burrito at $12 and fries at $4.
To show your subtotal, it might send SELECT SUM of price FROM items.
The burrito's 12 starts the total at 12.
The fries' 4 makes 16.
So it sends back 16, and the app shows a subtotal of $16.
Say Spotify adds a row every time you play a song, with the minutes you listened.
To show your listening time, it might send SELECT SUM of minutes FROM plays.
The first play's 3 starts the total at 3.
The second's 4 makes 7.
And the third's 2 makes 9.
So it sends back one row, holding 9.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT SUM(xp) FROM users WHERE xp > 50;.
What total comes back in the result?
Explanation
First, xp > 50 passes only Ada's 120, Dee's 300 and Fay's 75.
Then, SUM adds up those three values: .
Therefore, the result is one row holding 495.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT SUM(xp) FROM users WHERE xp < 100;.
What number comes back?
Say DoorDash keeps your order in a table called items, with a price column in dollars. Your order holds four items, priced 12, 4, 6 and 9. Its database gets SELECT SUM(price) FROM items WHERE price < 10;.
What number comes back?
Simplified's users table has five columns and 2,000,000 rows. Its server sends Postgres SELECT SUM(xp) FROM users;.
How many rows does the result have?
Say Lyft keeps your rides this week in a table called rides, with a cost column in dollars. You took three rides, costing $9, $16 and $15. Lyft's database gets SELECT COUNT(*) FROM rides; and then SELECT SUM(cost) FROM rides;.
What does each one send back?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75, so SELECT SUM(xp) FROM users; sends back 575. Then Ada finishes a lesson worth 10 XP, so her xp goes from 120 to 130.
What does the same query send back now?
Say Spotify adds a row to a plays table every time you play a song, with the minutes you listened. Your four plays lasted 3, 5, 2 and 4 minutes. Its database gets SELECT SUM(minutes) FROM plays WHERE minutes >= 3;.
What number comes back?
Databases · Lesson 7.3
Finding the average
So with users holding just Ada to Fay, SELECT SUM of XP FROM users sent back 575.
That's every learner's XP together, but not what a typical learner has earned.
So how does the analytics screen show the XP an average learner has earned?
An average adds up the values, then divides by how many there are.
AVG sends back the average of a column, over the rows that pass.
So the query says SELECT AVG of XP FROM users.
First, Postgres adds up the XP, just as SUM does: 575.
Then it counts how many values it added: 6.
And it divides 575 by 6.
That makes about 95.83.
So it sends back one row, holding about 95.83.
Say Amazon keeps a product's reviews in a table, one row for each review.
To show its rating, it might send SELECT AVG of stars FROM reviews.
Postgres adds 5, 4 and 3, to make 12.
Then it divides 12 by the 3 reviews: 4.
So the product shows 4 stars.
Say Duolingo keeps your XP for each day this week in a table: 7 rows.
To show your daily average, it might send SELECT AVG of XP FROM days.
Postgres adds up the week's XP, to make 210.
Then it divides 210 by the 7 days: 30.
So your average is 30 XP a day.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT AVG(xp) FROM users WHERE xp > 50;.
What average comes back in the result?
Explanation
First, xp > 50 passes only Ada's 120, Dee's 300 and Fay's 75, which add up to .
Then, AVG divides that total by the 3 values it added: .
Therefore, the result is one row holding 165.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT AVG(xp) FROM users WHERE xp >= 40;.
What number comes back?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT AVG(xp) FROM users WHERE xp < 100;.
What number comes back? Give it to 2 decimal places.
Say a YouTube channel keeps its videos in a table, with a views column. Its three videos have 1,200, 800 and 2,500 views. Its database gets SELECT AVG(views) FROM videos;.
What number comes back?
Say Spotify keeps your plays in a table, plays, with a minutes column. Your five plays lasted 6, 2, 4, 1 and 8 minutes. Its database gets SELECT AVG(minutes) FROM plays WHERE minutes > 3;.
What number comes back?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75, which add up to 575. Then a seventh learner, Gus, signs up from the USA, with 0 XP.
What does SELECT AVG(xp) FROM users; send back now? Give it to 2 decimal places.
Say Duolingo keeps your XP for each day in a table, days, with an xp column. Over four days you earned 30, 0, 50 and 0 XP. Its database gets SELECT AVG(xp) FROM days;.
What number comes back?
Databases · Lesson 7.4
One count for each group
So with users holding just Ada to Fay, SELECT AVG of XP FROM users sent back about 95.83.
That's one number for the whole table.
Simplified keeps another table too, attempts: one row for each answer a learner gives.
Each row holds the lesson ID of the question's lesson.
So lesson 1 got four answers, and it has four rows.
Now say the team wants to see how much practice each lesson gets.
SELECT COUNT star FROM attempts would count all 7 rows, and send back 7.
But how do you get one count for each lesson, not one for the whole table?
GROUP BY splits the rows into groups, one for each value of a column.
A group is the rows that share that value.
So the query says SELECT lesson ID, COUNT star FROM attempts.
Then GROUP BY lesson ID makes one group for each lesson.
It names lesson ID after SELECT too, so each count says which lesson it's for.
Postgres reads attempts one row at a time.
Row 1 is for lesson 1, so it starts lesson 1's group.
Row 2 is for lesson 2, so it starts lesson 2's group.
Row 3 is for lesson 1 again, so it joins lesson 1's group.
Row 4 starts lesson 3's group.
Then row 5 joins lesson 1's, row 6 lesson 2's, and row 7 lesson 1's.
So how many attempts did each lesson get?
COUNT star now counts each group on its own.
Lesson 1's group counts 1, 2, 3, 4.
Lesson 2's counts 1, 2.
And lesson 3's counts 1.
So Postgres sends back one row per group: a lesson ID, and its count.
Say Spotify adds a row for every song you play, with the artist and the minutes.
Grouped by artist, Drake's two plays make one group, and SZA's two another.
Then SUM adds up each group's minutes, instead of counting rows.
Drake's 3 and 4 make 7.
And SZA's 2 and 3 make 5.
So Postgres sends back one row per artist: Drake with 7, and SZA with 5.
Simplified's attempts table has 9 rows, one for each answer a learner gave. Their lesson_id values are 1, 2, 1, 3, 5, 2, 1, 5 and 1. Its server sends Postgres SELECT lesson_id, COUNT(*) FROM attempts GROUP BY lesson_id;.
How many rows come back in the result?
Explanation
First, GROUP BY lesson_id makes one group for each value of lesson_id: 1, 2, 3 and 5.
Then, COUNT(*) counts each group, and Postgres sends back one row per group: 1 with 4, 2 with 2, 3 with 1, and 5 with 2.
Therefore, 4 rows come back.
Say Instagram adds a row to a likes table every time someone likes one of your posts, with that post's id. The table has 7 rows, with post_id 10, 11, 10, 12, 10, 11 and 13. Its database gets SELECT post_id, COUNT(*) FROM likes GROUP BY post_id;.
What count comes back in post 10's row?
Say DoorDash adds a row to your orders table every time you order, with the month and the order's total in dollars. You have three orders: January for 20, February for 30 and January for 15. Its database gets SELECT month, SUM(total) FROM orders GROUP BY month;.
What number comes back in January's row?
Simplified's attempts table has 7 rows, one for each answer a learner gave, with the lesson_id of the question's lesson; 3 different lessons appear. SELECT COUNT(*) FROM attempts; sends back 7.
What does SELECT lesson_id, COUNT(*) FROM attempts GROUP BY lesson_id; send back instead?
Say Spotify adds a row to a plays table every time you play a song, with the artist and the minutes. You played Drake for 3 minutes, SZA for 2, Drake for 4, Taylor Swift for 5, SZA for 2 and SZA for 2. Its database gets SELECT artist, SUM(minutes) FROM plays GROUP BY artist;.
What number comes back in SZA's row?
Simplified's attempts table has 9 rows, one for each answer a learner gave, and every one of them has lesson_id 4. Its server sends Postgres SELECT lesson_id, COUNT(*) FROM attempts GROUP BY lesson_id;.
What does the result hold?
Simplified's attempts table has 5 rows, with lesson_id 1, 2, 1, 3 and 1. Its server sends Postgres SELECT lesson_id, COUNT(*) FROM attempts GROUP BY lesson_id;.
What is lesson 1's group?