# Creating a variable and filtering the output

Create a variable to calculate the total price for each item and use it to filter your output.

## Procedure

1. Under the UNNEST clause in the query, use the LET keyword to create a variable and assign to the variable an expression that calculates the total price for an item.

   You can do this by multiplying the price element by the qty element: LET $total = toDecimal(qty \* price)
2. Add a total\_price element with the value of the $total variable in the SELECT clause.

   The SELECT clause should look like this:
   ```
   SELECT {
       c.name,
       c.rating,
       o.orderno,
       itemno,
       total_price = $total
   }
   ```
3. Under the LET clause, add a WHERE clause to keep the items with a total price above 100: WHERE $total > 1000.
4. Under the JOIN clause, add another WHERE clause to keep the customers with a rating above 650: WHERE c.rating > 650.
5. To return an output with only the item with the highest total price, add the following clause after the SELECT clause: ORDER BY $total DESC LIMIT 1.

   This orders the output from highest to lowest total, and keeps only the first result.

## Results

The full query should look like this:

```
FROM order AS o
UNNEST o.items
LET $total = toDecimal(qty * price)
WHERE $total > 1000
JOIN customer AS c ON c.custid = o.custid
WHERE c.rating > 650
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
    total_price = $total
} ORDER BY $total DESC LIMIT 1
```

It return the following result:

```
[
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1002,
		"itemno": 460,
		"total_price": 9594.05
	}
]
```