Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Subquery and Calculations

Explore how to use subqueries with aggregate functions like AVG to filter query results. Understand aliases, WHERE clause subqueries, and alternate methods including subqueries in FROM clause and JOINs. This lesson helps you solve SQL problems using calculations and subqueries confidently.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to find the items and categories with more than average sales */
SELECT p.ProductName, p.Category
FROM   Products AS p 
WHERE  p.UnitsSold >
       (SELECT AVG(p2.UnitsSold) FROM Products AS p2)

Explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects the ProductName and Category columns from the Products table. The Products table has been given an alias p for easy referencing.

  • Line 3: The WHERE clause applies the condition that sales of UnitsSold is greater than the result of the subquery.

  • Line 4: The subquery returns the calculated average of all units sold of the products.

Recall of relevant concepts

We have covered the following concepts in this question: ...