title: How do I return a column from an aggregate subquery to the outer select statement that is using GROUP BY?
body: <p>I want to get better at subqueries which is why I am not using CTEs...
  I want to generate a table of unique agent IDs that shows count of transactions, sum of sales volume and sum of commission. I also want to show those same three aggregate columns in that table where the city equals x, y and z.</p>
  <pre><code>SELECT 
    agent_id
    , COUNT(transaction_id) AS count_all
    , (SELECT
         COUNT(transaction_id)
       FROM table1
       WHERE city = 'x'
       GROUP BY agent_id) AS count_x
  FROM table1
  GROUP BY agent_id
  </code></pre>
  <p>This is an example of what I wrote but I get an error returning &quot;Single-row subquery returns more than one row&quot;.</p>