title: SUM and COUNT for multiple condition in one query
body: <p>I am new to SQL. This is what i am trying.</p>
  <p>Table: <code>h_invoices</code> sample</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">InvNo (AI)</th>
  <th style="text-align: center;">InvCustomer</th>
  <th style="text-align: center;">InvTotal</th>
  <th style="text-align: center;">InvPayment</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">18</td>
  <td style="text-align: center;">Foo</td>
  <td style="text-align: center;">6750</td>
  <td style="text-align: center;">6750</td>
  </tr>
  <tr>
  <td style="text-align: center;">19</td>
  <td style="text-align: center;">Bar</td>
  <td style="text-align: center;">3300</td>
  <td style="text-align: center;">3300</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table: <code>h_invdetail</code> sample</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">indID (AI)</th>
  <th style="text-align: center;">invID</th>
  <th style="text-align: center;">ProductID</th>
  <th style="text-align: center;">UnitRate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">18</td>
  <td style="text-align: center;">18</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">3800</td>
  </tr>
  <tr>
  <td style="text-align: center;">19</td>
  <td style="text-align: center;">18</td>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">2950</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What i want is the count of <code>ProductA</code>, <code>ProductB</code> and so on based on <code>ProductID</code> and the <code>totalRevenue</code> which is <code>sum(InvTotal)</code>.</p>
  <p>What i have tried, for example in case of <strong>InvNo - 18</strong>:</p>
  <pre><code>SELECT 
      sum(case when h_invdetail.ProductId = 1 then 1 else 0 end) as ProductA,
      sum(case when h_invdetail.ProductId = 6 then 1 else 0 end) as ProductB,
      sum(case when h_invdetail.ProductId = 3 then 1 else 0 end) as ProductC, 
      sum(h_invoices.InvTotal) as REVENUE 
  FROM h_invdetail 
  LEFT JOIN h_invoices ON h_invdetail.invID = h_invoices.InvNo 
  WHERE h_invoices.InvNo = 18
  </code></pre>
  <p>I am getting sum as <strong>13500</strong> instead of <strong>6750</strong>. This is just for example, I have to get the Revenue and product distribution for the whole table, but i am getting wrong value for <code>sum(h_invoices.InvTotal)</code>. Am i missing something?</p>