title: MySQL show data if value greater than average
body: <p>I have this query where I want to select or show authors whose number of books is above the average.</p>
  
  <p>So far, the query I got to get the average number of books is the following:</p>
  
  <pre><code>SELECT AVG(c.quantity) AS 'Average'
  FROM(
  SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
  FROM autores_has_libros AS al
  GROUP BY al.autores_DNI
  ) AS c
  </code></pre>
  
  <p>How can I then select (show) only those authors whose number of books is above the average?</p>
  
  <p>This query lists the authors with their number of books:</p>
  
  <pre><code>SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
      FROM autores_has_libros AS al
      GROUP BY al.autores_DNI
  </code></pre>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT a.first_name 
  FROM autores AS a
  HAVING (
  SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
      FROM autores_has_libros AS al
      GROUP BY al.autores_DNI
  )
  &gt; (
  SELECT AVG(c.quantity) AS 'Average'
      FROM(
      SELECT al.autores_DNI AS 'Author',COUNT(*) AS 'quantity'
      FROM autores_has_libros AS al
      GROUP BY al.autores_DNI
      ) AS c
  )
  </code></pre>
  
  <p>But that doesn't work</p>