title: MySQL view subquery with custom where clause?
body: <p>I am trying to design a view on a MySQL database that stores information about football (soccer).</p>
  
  <p>My goal is to create a view that returns all the goals and the basic information of the scorers. This is a simplified version of what the view used to look like:</p>
  
  <pre><code>SELECT P.Name AS Player,
         P.TeamName AS Team,
         (SELECT COUNT(*)
          FROM Goals G
          WHERE G.Scorer = P.playerID) AS TotalGoals
  FROM Players P
  </code></pre>
  
  <p>So far, everything's right. The problem started when I wanted to count goals only within a certain season, specified in the <code>WHERE</code> clause, as I show below:</p>
  
  <pre><code>SELECT * FROM GoalsView WHERE Season = 1
  </code></pre>
  
  <p>And for it to return automatically only the count of the goals of the season in <code>WHERE</code> clause, as if the subquery was automatically edited to make it look like:</p>
  
  <pre><code>[...]
  (SELECT COUNT(*)
  FROM Goals G
  WHERE G.Scorer = P.playerID
  AND Season=1) AS TotalGoals
  [...]
  </code></pre>
  
  <p>I hope I made myself clear. Thanks for your help!</p>