title: SQL conditional grouping and summing
body: <p>My data looks like this:</p>
  
  <pre><code>|cat |subcat |amount|
  ---------------------
  |A   |1      |123   |
  |A   |2      |456   |
  |B   |1      |222   |
  |B   |2      |333   |
  </code></pre>
  
  <p>In the first case, I need to sum by cat and subcat.  Easy:</p>
  
  <pre><code>SELECT cat, subcat, sum(amount) FROM data GROUP BY cat, subcat
  </code></pre>
  
  <p>Next, I have a more sophisticated requirement where for certain cats, the amount should be "pushed" into a given subcat.  This can be stored in another <code>config</code> table:</p>
  
  <pre><code>|cat |subcat|
  -------------
  |B   |1     |
  </code></pre>
  
  <p>This tells me that for all <code>cat='B'</code> rows, the amount should be treated as a <code>subcat=1</code>.  Furthermore, where <code>cat='B' AND subcat &lt;&gt; 1</code> the amount should be reported as zero. In other words, the result I need is:</p>
  
  <pre><code>|cat |subcat|amount|
  |A   |1     |123   |
  |A   |2     |456   |
  |B   |1     |555   |
  |B   |2     |0     |
  </code></pre>
  
  <p>I cannot update my data table.  Of course I can <code>SELECT ... INTO</code> in a proc and fix the data there, but I'm wondering if it can be done in one hit.</p>
  
  <p>I can get pretty close with: </p>
  
  <pre><code>SELECT data.cat,
      ISNULL(config.subcat, data.subcat),
      SUM(amount)
  FROM data
      LEFT OUTER JOIN config ON (data.cat = config.cat)
  GROUP BY data.cat, ISNULL(config.subcat, data.subcat)
  </code></pre>
  
  <p>...but fails my second requirement to show <code>cat:B, subcat:2</code> as zero.</p>
  
  <p>Is it possible?</p>
  
  <p>I'm using Sybase IQ 12.5 (i.e. old T-SQL, but is has the <code>case</code> statement, which I suspect might be useful)</p>