To validate that the seat pricing system is balanced between many factors, the testing teams needs an analysis of the variations in pricing for a given passenger. In this case Jon Cooley will be the selected passenger $($PassID# $12).$ The results need to show the FlightID, along with the assigned seat price was and how it compares to the passenger's overall average seat price. In other words, the average is based on all of Jon's flights, not all passengers.

Once the average is determined the difference should be displayed for quick lookup and comparison.

Include fields shown in the example below

Use a Sub$-$Query to first calculate the average seat price, AvgSeatPrice, for Jon. Then integrate that into the primary query to produce the final results.

You will need the PassID and AvgSeatPrice in the sub$-$query to properly join the results of the subquery into the primary query.

Sort by FlightID

Label the query as Query $1$

Example results of first $3$ rows:

$\backslash$table$[[$FirstName$,$LastName,FlightID,SeatPrice,AvgSeatPrice,Difference$],[$Jon$,$Cooley,$63,40\mathrm{.}88,36\mathrm{.}80,4\mathrm{.}08],[$Jon$,$Cooley,$104,22\mathrm{.}11,36\mathrm{.}80,-14\mathrm{.}69],[$Jon$,$Coolev,$153,24\mathrm{.}25,36\mathrm{.}80,-12\mathrm{.}55]]$