title: How to group multiple subqueries in the same output row
body: <p>I'm trying to extract transaction details from two already existing tables:</p>
  
  <ol>
  <li><p><code>transactions</code>, containing the total amount received, </p></li>
  <li><p><code>bills</code>, with a row for each bill received in the transaction, and containing the denomination of the bill. </p></li>
  </ol>
  
  <p>Both are indexed with a common <code>session</code> id. [<em>Correction:</em> only the <code>transactions</code> table is indexed on <code>session</code> id.]</p>
  
  <p>I've joined the tables and made subqueries to count the number of each bill denomination per transaction (how many 10s, 20s, etc.). I want to get one record for each transaction with all the counts on the same row.</p>
  
  <p>I made it as far as this query:</p>
  
  <pre><code>SELECT
  t.session,
  to_char(t.amount::numeric, '"$"9990D99') AS "USD",
  (select count(b.denom) where b.denom = '50' ) AS "50",
  (select count(b.denom) where b.denom = '20') AS "20",
  (select count(b.denom) where b.denom = '10') AS "10",
  (select count(b.denom) where b.denom = '5') AS "5",
  (select count(b.denom) where b.denom = '1') AS "1"
  
  FROM transactions AS t JOIN bills AS b USING (session)
  GROUP BY
  t.session, t.amount, b.denom
  ORDER BY 
  t.session,
  b.denom ASC;
  </code></pre>
  
  <p>... which correctly gives me the bill counts, but with one row for each denomination:</p>
  
  <pre><code>   session    |    USD    | 50 | 20 | 10 | 5 | 1
  --------------+-----------+----+----+----+---+----
   c64af32f1815 | $  135.00 |    |    |    | 1 |
   c64af32f1815 | $  135.00 |    |    |  1 |   |
   c64af32f1815 | $  135.00 |    |  6 |    |   |
   643e096b6542 | $  175.00 |    |    |    |   | 10
   643e096b6542 | $  175.00 |    |    |    | 1 |
   643e096b6542 | $  175.00 |    |  8 |    |   |
   ce7d2c647eff | $  200.00 |  4 |    |    |   |
  </code></pre>
  
  <p>What I want is this, with one row per transaction:</p>
  
  <pre><code>   session    |    USD    | 50 | 20 | 10 | 5 | 1
  --------------+-----------+----+----+----+---+----
   c64af32f1815 | $  135.00 |    |  6 |  1 | 1 |
   643e096b6542 | $  175.00 |    |  8 |    | 1 | 10
   ce7d2c647eff | $  200.00 |  4 |    |    |   |
  </code></pre>
  
  <p>What do I need to understand to fix this query?</p>
  
  <p><strong>Revised Query</strong> (following @erwin suggestion to avoid subqueries):</p>
  
  <pre><code>SELECT
  t.session,
  to_char(t.amount::numeric, '"$"9990D99') AS "USD",
  COUNT(NULLIF(b.denom = '100', FALSE)) AS "100",
  COUNT(NULLIF(b.denom = '50', FALSE)) AS "50",
  COUNT(NULLIF(b.denom = '20', FALSE)) AS "20",
  COUNT(NULLIF(b.denom = '10', FALSE)) AS "10",
  COUNT(NULLIF(b.denom = '5', FALSE)) AS "5",
  COUNT(NULLIF(b.denom = '1', FALSE)) AS "1"
  
  FROM transactions AS t JOIN bills AS b USING (session)
  GROUP BY
  t.session, t.amount, b.denom
  ORDER BY 
  t.session,
  b.denom ASC;
  </code></pre>
  
  <p>This query still generates one line of output for each aggregate (count) function call.</p>