title: sql with multiple counts against one child table
body: <p>DB2 database. A Request can have multiple Transactions. I'd like to query the request and get a count of different transaction types. So request 1 with four transactions (AA, BB, BB, CC) would result:
  REQ=1234, REQ_USER='Susan', REQ_ACCT=223344, CNT_AA=1, CNT_BB=2, CNT_CC=3</p>
  
  <p>This query gets me an accurate count of one transaction type:</p>
  
  <pre><code>SELECT COUNT(*) AS TRANS_AA
  FROM TABLE1 AS T1, TABLE2 AS T2
  WHERE T1.REQUEST_ID=1234 
  AND T1.COMPONENTID = T2.PARENTCOMPID
  AND T2.TRANS_TYPE='AA';
  </code></pre>
  
  <p>If I also want the request id, this query gives me that but I need to 'Group By' request id:</p>
  
  <pre><code>SELECT T1.REQUEST_ID, COUNT(*) AS TRANS_AA
  FROM TABLE1 AS T1, TABLE2 AS T2
  WHERE T1.REQUEST_ID=1234 
  AND T1.COMPONENTID = T2.PARENTCOMPID
  AND T2.TRANS_TYPE='AA'
  GROUP BY T1.REQUEST_ID;
  </code></pre>
  
  <p>I'm not sure how to add several columns from T1 (user, account, etc) and several counts of transaction types (let's say we have 10 different types). I've tried some INNER JOINs between the two, but am not sure if this can be done efficiently, or one JOIN for all counts, or multiple joins, etc.</p>
  
  <p>I posted a sample of the data I'd like to get back, but per request below.</p>
  
  <pre><code>TABLE1
  componentid (key)
  request_id
  req_user
  req_account
  ... 27 more columns
  
  TABLE 2 (child table, could have many trans_types for T1)
  parentcompid (foreign key to componentid in Table1)
  trans_type
  </code></pre>