title: Efficient use of SQL GROUP BY, SUM, COUNT
body: <p>I have a table of products sales that may look as follows:</p>
  
  <pre><code>product   | amount   | ptype    | pdate      
  p1        | 1.00     | sale     | 01/01
  p1        | 2.00     | base     | 01/02
  p2        | 1.50     | sale     | 02/03
  p3        | 5.25     | base     | 10/10
  </code></pre>
  
  <p>and I would like to build a table that shows one product per row, the sum of the amounts, if the product is unique show the type else show the type as 'VAR', if the product is unique show the date else show the date as NULL. So that the result look as follows:</p>
  
  <pre><code>product   | total    | ptype    | pdate      
  p1        | 3.00     | VAR      | (NULL)
  p2        | 1.50     | sale     | 02/03
  p3        | 5.25     | base     | 10/10
  </code></pre>
  
  <p>I am accomplishing the result I need by doing the following: </p>
  
  <pre><code>SELECT DISTINCT product
  ,(SELECT SUM(amount) FROM T as b GROUP BY b.product HAVING a.product = b.product ) as total
  ,(SELECT CASE WHEN COUNT(*) = 1 THEN a.ptype  ELSE 'VAR' END from T as b GROUP BY b.product HAVING a.product = b.product) as ptype
  ,(SELECT CASE WHEN COUNT(*) = 1 THEN a.pdate  ELSE NULL END from T as b GROUP BY b.product HAVING a.product = b.product) as pdate
  FROM T as a
  </code></pre>
  
  <p>But I would like to know if there is a more efficient way that accomplishes the same result. </p>