title: How to query multiple SUMs into multiple columns of the same SQL query in iReport?
body: <p>Is this efficient? How could it be improved?</p>
  
  <p>I was trying to get data from one set of columns into two sets of columns according to a condition. All the methods I tried ended up with multiple rows. </p>
  
  <p>This is very similar to <a href="https://stackoverflow.com/questions/1002639/how-to-query-multiple-sums-of-the-same-item-using-sql-in-ireport">this other question</a> but a bit more complicated.</p>
  
  <p>Here's what I did:
  (Because this is for iReport/JasperReports, it would have been a major hassle to use multiple queries.) Basically we want to get the statistics for living friendly and non-friendly soldiers from two tables for an army war game exercise.</p>
  
  <p><pre><code>
  SELECT 
     army.army_key UNIT_NUM,
     COUNT( case when soldier.type_id = 1 then 1 else NULL end ) AS friendCount, 
     SUM( case when soldier.type_id = 1 then soldier.achievement else 0 end ) AS friendAmount, 
     COUNT( case when soldier.type_id = 2 then 1 else NULL end ) AS enemyCount, 
     SUM( case when soldier.type_id = 2 then soldier.achievement else 0 end ) AS enemyAmount
     FROM soldier, army
     WHERE soldier.army_key = army.army_key 
        AND army.wargame_id = $P{GAME_ID} 
        AND soldier.dead is null 
     GROUP BY army.army_key
     ORDER BY UNIT_NUM ASC
  </pre></code></p>