Message from @Jacob
Discord ID: 549085521339613184
Do we have any actuaries or CFAs in here?
Pic somewhat related. I want to work with him.
😂
okay can someone help me with this?
Determine which books cost less than the average cost of books in the same category.List the title, the category, and the cost of these books.
apparently this is the answer, but I don't really get it
```SQL
SELECT a.title, b.category, a.cost
FROM books a,
(SELECT category, AVG(cost) averagecost
FROM books
GROUP BY category) b
WHERE a.category = b.category
AND a.cost < b.averagecost;
```
I see no numbers
it's not a math question
it's just SQL
oh ya I forgot to tag <@&435155896780324864>
I ended up writing my own solution like this
```SQL
SELECT BOOKS.Title, BOOKS.Category, BOOKS.Cost
FROM BOOKS, (SELECT BOOKS.Category, AVG(BOOKS.Cost) AverageCost
FROM BOOKS
GROUP BY BOOKS.Category) AverageCosts
WHERE BOOKS.Category = AverageCosts.Category
AND BOOKS.Cost < AverageCosts.AverageCost
```
idk if the first one is better
mine feels less convoluted
@Jacob I agree yours is less convoluted that the first one, but I would do
```sql
SELECT Title, Category, Cost FROM
(
BOOKS
INNER JOIN
(
SELECT Category, AVG(Cost) as avg_cost FROM
BOOKS GROUP BY Category
)
)
WHERE Cost < avg_cost
```
haha wow that's a lot less
I think our teacher wants us to get practice with subqueries on this assignment, though
@Jacob well the avg cost part is calculated with a subquery
oh that's right
but that looks like a better solution for the future, thanks