Question: I'm having trouble writing a MySQL subquery.

The SALES table has the column names:

```
sales_date | staff_id | sales_amount

```

to record each individual employee's sales.

What I'm trying to achieve with this query is a table that displays the average daily sales for a selected employee as well as the average daily sales for all employees. I only want to display days when the selected employee had any sales.

```
SELECT staff_id, sales_date, AVG( sales_amount ) AS avgsales, sales_amount
FROM sales
GROUP BY sales_date
WHERE 
    (SELECT sales_date
    FROM sales
    WHERE staff_id = $staff_id) // this subquery shows all the dates that staff has records in

```

I want it to display something like this:

```
   Staff_id | Sales date | AverageSales | Employee_sales
   ---------+------------+--------------+----------------
   001      | 2016-04-18 | £2000        | £1800
   ---------+------------+--------------+----------------
   001      | 2016-04-17 | £3405        | £4000
   ---------+------------+--------------+----------------
   001      | 2016-04-16 | £1450        | £1400
   ---------+------------+--------------+----------------

```

Answer: 
I use Oracle, so there will likely be a typo in this MySQL SQL. But, I hope it works properly...

You have two concepts: Average sales for everyone per day and average sales per user per day. You want to join those two things together. You can easily.

First, you have the average sales per day.

```
select sales_date, avg(sales_amount) as averagesales
from sales
group by sales_date

```

Now, you want the average sales per user per day.

```
select staff_id, sales_date, avg(sales_amount) as employeesales
from sales
group by sales_date, staff_id

```

You want those joined together. That is easy.

```
select * from (
  select sales_date, avg(sales_amount) as averagesales
  from sales
  group by sales_date
)a join (
  select staff_id, sales_date, avg(sales_amount) as employeesales
  from sales
  group by sales_date, staff_id
)b on a.sales_date=b.sales_date

```

That will give you staff\_id, sales\_date, average sales for the day, and employee average sales for the day in one query. You will likely want to order it just how you like, limit the dates, and other things.
You can join a table to itself in a query, rather than use a subquery. Try this:

```
SELECT sales.staff_id, sales.sales_date, AVG(sales.sales_amount), AVG(emp.sales_amount)
FROM sales
LEFT JOIN sales emp ON (emp.staff_id=$staff_id)
WHERE sales.staff_id=$staff_id
GROUP BY sales_date;

```

So essentially this creates a second table containing only the selected employee's sales, which you can then average.