Simplified
Simplified

L7.1One number from millions of rows

YOU

One number from millions of rows

SELECTname, xpFROM usersORDER BY xp DESC, nameLIMIT 3;resultnamexpDee300Ada120Fay75Dee300Ada120Fay75
SELECTidFROM users;SELECTCOUNT(*)FROM users;usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75id123456nameAdaBenCyDeeEliFayxp12040030040751Ada1202Ben403Cy04Dee3005Eli406Fay751Ada1202Ben403Cy04Dee3005Eli406Fay75⋮ 2,000,000 rows1234562,000,000resultid123456123456⋮ 2,000,000 rowsresultcount2,000,0002,000,000Simplified analytics2,000,000learners?
SELECTCOUNT(*)FROM followers;followersidfollower1@maya2@leo3@sam4@joid1234follower@maya@leo@sam@jo1@maya2@leo3@sam4@jo1@maya2@leo3@sam4@jo⋮ 312 rows1234312resultcount312312Instagram: your profile312Followers
SELECTCOUNT(*)FROM productsWHERE name ILIKE '%headphones%';productsidname1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphonesid12345nameSony HeadphonesUSB cableBose headphonesPhone caseJBL Headphones1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphones1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphones⋮ millions of rows1231,204resultcount1,2041,204resultcount00Amazon: headphones1,204results

Databases · Lesson 7.1

Counting

One number from millions of rows

Simplified
0:00 / 1:49

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.

SELECTidFROM users;SELECTCOUNT(*)FROM users;usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75id123456nameAdaBenCyDeeEliFayxp12040030040751Ada1202Ben403Cy04Dee3005Eli406Fay751Ada1202Ben403Cy04Dee3005Eli406Fay75⋮ 2,000,000 rows1234562,000,000resultid123456123456⋮ 2,000,000 rowsresultcount2,000,0002,000,000Simplified analytics2,000,000learners

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.

SELECTidFROM users;SELECTCOUNT(*)FROM users;usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75id123456nameAdaBenCyDeeEliFayxp12040030040751Ada1202Ben403Cy04Dee3005Eli406Fay751Ada1202Ben403Cy04Dee3005Eli406Fay75⋮ 2,000,000 rows1234562,000,000resultid123456123456⋮ 2,000,000 rowsresultcount2,000,0002,000,000Simplified analytics2,000,000learners

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.

SELECTCOUNT(*)FROM followers;followersidfollower1@maya2@leo3@sam4@joid1234follower@maya@leo@sam@jo1@maya2@leo3@sam4@jo1@maya2@leo3@sam4@jo⋮ 312 rows1234312resultcount312312Instagram: your profile312Followers

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.

SELECTCOUNT(*)FROM productsWHERE name ILIKE '%headphones%';productsidname1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphonesid12345nameSony HeadphonesUSB cableBose headphonesPhone caseJBL Headphones1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphones1Sony Headphones2USB cable3Bose headphones4Phone case5JBL Headphones⋮ millions of rows1231,204resultcount1,2041,204resultcount00Amazon: headphones1,204results

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.

Example: One number from millions of rows

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.

Question 1

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?

rows counted

Adding up a column

SELECTCOUNT(*)FROM users;SELECTSUM(xp)FROM users;usersnamexpAda120Ben40Cy0Dee300Eli40Fay75nameAdaBenCyDeeEliFayxp1204003004075Ada120Ben40Cy0Dee300Eli40Fay75Ada120Ben40Cy0Dee300Eli40Fay75⋮ 2,000,000 rowsrunning total120+ 40= 160+ 0= 160+ 300= 460+ 40= 500+ 75= 575resultcount2,000,0002,000,000resultsum575575Simplified analytics575XP earned?
SELECTSUM(price)FROM items;itemsitempriceBurrito12Fries4itemBurritoFriesprice124Burrito12Fries4Burrito12Fries412+ 4= 16resultsum1616DoorDash: your order$16Subtotal
SELECTSUM(minutes)FROM plays;playssongminutesEspresso3Not Like Us4Saturn2songEspressoNot Like UsSaturnminutes342Espresso3Not Like Us4Saturn2Espresso3Not Like Us4Saturn23+ 4= 7+ 2= 9resultsum99Spotify: this week9minutes listened

Databases · Lesson 7.2

Counting

Adding up a column

Simplified
0:00 / 1:44

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.

SELECTCOUNT(*)FROM users;SELECTSUM(xp)FROM users;usersnamexpAda120Ben40Cy0Dee300Eli40Fay75nameAdaBenCyDeeEliFayxp1204003004075Ada120Ben40Cy0Dee300Eli40Fay75Ada120Ben40Cy0Dee300Eli40Fay75⋮ 2,000,000 rowsrunning total120+ 40= 160+ 0= 160+ 300= 460+ 40= 500+ 75= 575resultcount2,000,0002,000,000resultsum575575Simplified analytics575XP earned

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.

SELECTSUM(price)FROM items;itemsitempriceBurrito12Fries4itemBurritoFriesprice124Burrito12Fries4Burrito12Fries412+ 4= 16resultsum1616DoorDash: your order$16Subtotal

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.

SELECTSUM(minutes)FROM plays;playssongminutesEspresso3Not Like Us4Saturn2songEspressoNot Like UsSaturnminutes342Espresso3Not Like Us4Saturn2Espresso3Not Like Us4Saturn23+ 4= 7+ 2= 9resultsum99Spotify: this week9minutes 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.

Example: Adding up a column

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: 120+300+75=495120 + 300 + 75 = 495120+300+75=495.

Therefore, the result is one row holding 495.

Question 1

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?

XP

Question 2

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?

dollars

Question 3

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?

Question 4

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?

Question 5

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?

XP

Question 6

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?

minutes

Finding the average

SELECTSUM(xp)FROM users;SELECTAVG(xp)FROM users;usersnamexpAda120Ben40Cy0Dee300Eli40Fay75nameAdaBenCyDeeEliFayxp1204003004075Ada120Ben40Cy0Dee300Eli40Fay75Ada120Ben40Cy0Dee300Eli40Fay75123456resultsum575575resultavg95.83…95.83…120+ 40+ 0+ 300+ 40+ 75= 575575 ÷ 6= 95.83…Simplified analytics95.83average XP?
SELECTAVG(stars)FROM reviews;reviewsidstars152433id123stars543152433152433resultavg445+ 4+ 3= 1212 ÷ 3= 4Amazon: reviews4stars
SELECTAVG(xp)FROM days;daysdayxpMon20Tue35Wed10Thu50Fri15Sat40Sun40dayMonTueWedThuFriSatSunxp20351050154040Mon20Tue35Wed10Thu50Fri15Sat40Sun40Mon20Tue35Wed10Thu50Fri15Sat40Sun40resultavg303020+ 35+ 10+ 50+ 15+ 40+ 40= 210210 ÷ 7= 30Duolingo: this week30XP a day, on average

Databases · Lesson 7.3

Counting

Finding the average

Simplified
0:00 / 1:32

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.

SELECTSUM(xp)FROM users;SELECTAVG(xp)FROM users;usersnamexpAda120Ben40Cy0Dee300Eli40Fay75nameAdaBenCyDeeEliFayxp1204003004075Ada120Ben40Cy0Dee300Eli40Fay75Ada120Ben40Cy0Dee300Eli40Fay75123456resultsum575575resultavg95.83…95.83…120+ 40+ 0+ 300+ 40+ 75= 575575 ÷ 6= 95.83…Simplified analytics95.83average XP

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.

SELECTAVG(stars)FROM reviews;reviewsidstars152433id123stars543152433152433resultavg445+ 4+ 3= 1212 ÷ 3= 4Amazon: reviews4stars

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.

SELECTAVG(xp)FROM days;daysdayxpMon20Tue35Wed10Thu50Fri15Sat40Sun40dayMonTueWedThuFriSatSunxp20351050154040Mon20Tue35Wed10Thu50Fri15Sat40Sun40Mon20Tue35Wed10Thu50Fri15Sat40Sun40resultavg303020+ 35+ 10+ 50+ 15+ 40+ 40= 210210 ÷ 7= 30Duolingo: this week30XP a day, on average

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.

Example: Finding the average

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 120+300+75=495120 + 300 + 75 = 495120+300+75=495.

Then, AVG divides that total by the 3 values it added: 495÷3=165495 \div 3 = 165495÷3=165.

Therefore, the result is one row holding 165.

Question 1

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?

XP

Question 2

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.

XP

Question 3

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?

views

Question 4

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?

minutes

Question 5

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.

XP

Question 6

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?

XP

One count for each group

SELECTSUM(xp)FROM users;SELECTAVG(xp)FROM users;usersnamexpAda120Ben40Cy0Dee300Eli40Fay75nameAdaBenCyDeeEliFayxp1204003004075Ada120Ben40Cy0Dee300Eli40Fay75Ada120Ben40Cy0Dee300Eli40Fay75123456resultsum575575resultavg95.83…95.83…120+ 40+ 0+ 300+ 40+ 75= 575575 ÷ 6= 95.83…Simplified analytics95.83average XP
SELECTCOUNT(*)FROM attempts;SELECTlesson_id,COUNT(*)FROM attemptsGROUP BY lesson_id;attemptsidlesson_id11223143516271id1234567lesson_id121312111223143516271112231435162711234567resultcount7lesson 141234lesson 2212lesson 31111223143516271resultlesson_idcount142231lesson_id123count421?
SELECTartist,SUM(minutes)FROM playsGROUP BY artist;playsartistminutesDrake3SZA2Drake4SZA3artistDrakeSZADrakeSZAminutes3243Drake3SZA2Drake4SZA3Drake3SZA2Drake4SZA33 + 4= 72 + 3= 5Drake3SZA2Drake4SZA3resultartistsumDrake7SZA5artistDrakeSZAsum75

Databases · Lesson 7.4

Counting

One count for each group

Simplified
0:00 / 2:07

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.

SELECTCOUNT(*)FROM attempts;SELECTlesson_id,COUNT(*)FROM attemptsGROUP BY lesson_id;attemptsidlesson_id11223143516271id1234567lesson_id121312111223143516271112231435162711234567resultcount7lesson 141234lesson 2212lesson 31111223143516271resultlesson_idcount142231lesson_id123count421

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.

SELECTCOUNT(*)FROM attempts;SELECTlesson_id,COUNT(*)FROM attemptsGROUP BY lesson_id;attemptsidlesson_id11223143516271id1234567lesson_id121312111223143516271112231435162711234567resultcount7lesson 141234lesson 2212lesson 31111223143516271resultlesson_idcount142231lesson_id123count421

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.

SELECTCOUNT(*)FROM attempts;SELECTlesson_id,COUNT(*)FROM attemptsGROUP BY lesson_id;attemptsidlesson_id11223143516271id1234567lesson_id121312111223143516271112231435162711234567resultcount7lesson 141234lesson 2212lesson 31111223143516271resultlesson_idcount142231lesson_id123count421

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?

SELECTCOUNT(*)FROM attempts;SELECTlesson_id,COUNT(*)FROM attemptsGROUP BY lesson_id;attemptsidlesson_id11223143516271id1234567lesson_id121312111223143516271112231435162711234567resultcount7lesson 141234lesson 2212lesson 31111223143516271resultlesson_idcount142231lesson_id123count421

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.

SELECTartist,SUM(minutes)FROM playsGROUP BY artist;playsartistminutesDrake3SZA2Drake4SZA3artistDrakeSZADrakeSZAminutes3243Drake3SZA2Drake4SZA3Drake3SZA2Drake4SZA33 + 4= 72 + 3= 5Drake3SZA2Drake4SZA3resultartistsumDrake7SZA5artistDrakeSZAsum75

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.

Example: One count for each group

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.

Question 1

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?

likes

Question 2

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?

dollars

Question 3

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?

Question 4

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?

Question 5

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?

Question 6

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?

Assistant

Highlight lesson text and click Ask AI, or type a question below.