title: How to write this query for three tables
body: <p>I have 3 tables</p>
  
  <p>Table: <strong>agents</strong></p>
  
  <pre><code>Id | Name
  ---+------
   1 | a
   2 | b
   3 | c
  </code></pre>
  
  <p>Table: <strong>passengers</strong></p>
  
  <pre><code>Id | AgentReference | Name
  ---+----------------+-----
   1 |       1        | E
   2 |       1        | F
   3 |       3        | ddd
  </code></pre>
  
  <p>Table: <strong>visa_by_agent</strong></p>
  
  <pre><code>Id | agentId | visa_id | Total | Used
  ---+---------+---------+-------+------
   1 |   1     | aa      |   10  |   1
   2 |   1     | bb      |   20  |   2
   3 |   3     | dd      |   10  |   5
  </code></pre>
  
  <p>I want to show the result like</p>
  
  <pre><code>agentId | agentName | TotalPassenger | TotalVisa | TotalUsed
  --------+-----------+----------------+-----------+-----------
     1    | a         |      2         |     30    |     3
     2    | b         |      0         |      0    |     0
     3    | c         |      1         |     10    |     5
  </code></pre>
  
  <p>I tried with this and getting syntax error</p>
  
  <pre><code>select  
      a.Id, a.Name, 
      vba.total, vba.used p.num_passengers 
  from 
      agents a 
  left join 
      (select 
           vba.AgentId, sum(Total) as Total, sum(Used) as Used 
       from 
           visa_by_agent vba 
       group by 
           vba.AgentId) vba on a.Id = vba.AgentId 
  left join 
      (select p.AgentReference, count(*) as num_passengers 
       from passengers p 
       group by p.AgentReference) p on a.Id = p.AgentReference 
  group by 
      agents.Id
  </code></pre>