WEBVTT 00:00:00.350 --> 00:00:01.331 Welcome back! 00:00:01.511 --> 00:00:03.175 Here's what we'll learn in this lesson. 00:00:03.355 --> 00:00:04.977 Summarize related records. 00:00:05.157 --> 00:00:10.811 We will build invoice, customer and product totals, ask for counts and averages, then filter 00:00:10.811 --> 00:00:12.901 products by average selling price. 00:00:13.081 --> 00:00:15.577 We reuse the row formula from lesson four. 00:00:16.950 --> 00:00:19.425 Let's give each invoice a calculated total. 00:00:19.605 --> 00:00:22.079 Declare the calculation once in the model. 00:00:22.259 --> 00:00:25.097 Select its rows and add their calculated amounts. 00:00:25.277 --> 00:00:27.581 THIS id is the current invoice's key. 00:00:28.911 --> 00:00:31.535 Let's read those totals as ordinary fields. 00:00:31.715 --> 00:00:34.019 The query only asks for number and total. 00:00:34.199 --> 00:00:36.140 The calculation stays in the model. 00:00:37.550 --> 00:00:39.961 Let's total the invoices of each customer. 00:00:40.141 --> 00:00:42.615 Reuse the invoice total we just declared. 00:00:42.795 --> 00:00:45.142 THIS id now belongs to the customer. 00:00:47.195 --> 00:00:49.435 Let's read each customer's sales total. 00:00:49.615 --> 00:00:51.087 Ada has two invoices. 00:00:51.267 --> 00:00:53.827 Together they make one hundred fifty-four ten. 00:00:55.250 --> 00:00:57.405 Let's total the sales of each product. 00:00:57.585 --> 00:01:00.273 Add the amounts of invoice rows carrying its code. 00:01:00.453 --> 00:01:03.375 This is sales revenue, not the quantity in stock. 00:01:05.346 --> 00:01:06.989 Let's read the product totals. 00:01:07.169 --> 00:01:09.452 Again, the query uses a named field. 00:01:09.632 --> 00:01:12.042 Products with no sales have a null total. 00:01:13.450 --> 00:01:16.031 Now let's ask for several aggregates at once. 00:01:16.211 --> 00:01:20.883 A customer reaches its invoices through the inverse name declared in lesson two. 00:01:21.063 --> 00:01:23.773 Each customer still produces one result row. 00:01:25.103 --> 00:01:27.492 Let's summarize each customer's invoices. 00:01:27.672 --> 00:01:32.003 Aggr adds their totals, counts the invoices, and averages their amounts. 00:01:32.183 --> 00:01:35.063 Asqueel names the returned columns automatically. 00:01:36.393 --> 00:01:38.483 Let's count the rows of each invoice too. 00:01:38.663 --> 00:01:41.330 The same aggregation syntax follows rows. 00:01:41.510 --> 00:01:45.521 Count returns the number of invoice rows, not the sum of their quantities. 00:01:46.950 --> 00:01:49.339 Let's average the recorded selling prices. 00:01:49.519 --> 00:01:53.295 Follow a product's invoice rows and average their unit prices. 00:01:53.475 --> 00:01:56.483 This is one price per row, without weighting by quantity. 00:01:57.867 --> 00:02:00.640 Let's name the average price so we can filter it. 00:02:00.820 --> 00:02:02.527 Define the average in the model. 00:02:02.707 --> 00:02:08.531 In this release, aggr returns columns; a named formula also works in where and order by. 00:02:09.861 --> 00:02:11.738 Let's filter by average price. 00:02:11.918 --> 00:02:15.971 Choose three twenty to four eighty, passing both bounds as parameters. 00:02:16.151 --> 00:02:17.879 Between includes the endpoints. 00:02:18.059 --> 00:02:21.409 Sort by average price, and read the six matching products. 00:02:22.783 --> 00:02:24.277 Thanks for following along! 00:02:24.457 --> 00:02:28.297 We summarized related records while keeping one result per parent. 00:02:28.477 --> 00:02:31.826 Next, group by will combine sales rows into reports. 00:02:32.006 --> 00:02:35.569 We will use where and having to filter before and after grouping.