MySQL sub query on the same table
<p>I'm having trouble writing a MySQL subquery.</p>

<p>The SALES table has the column names:</p>

<pre><code>sales_date | staff_id | sales_amount
</code></pre>

<p>to record each individual employee's sales.</p>

<p>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.</p>

<pre><code>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
</code></pre>

<p>I want it to display something like this:</p>

<pre><code>   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
   ---------+------------+--------------+----------------
</code></pre>