title: self join with sum field
body: <p>I have data like this
  table t_prod</p>
  
  <pre><code>+---------+----------+-------------+-----------+----------+
  |t_prod_id|t_prod_lot|t_prod_sublot|t_prod_card|t_prod_qty|
  +---------+----------+-------------+-----------+----------+
  |    4    |   R001   |      1      |      1    |   6000   |
  |    5    |   R001   |      1      |      2    |   6000   |
  |    6    |   R001   |      1      |      3    |   6000   |
  |    10   |   R001   |      1      |      4    |   6000   |
  |    11   |   R001   |      1      |      5    |   6000   |
  |    12   |   R001   |      1      |      6    |   6000   |
  |    13   |   R001   |      2      |      1    |   6000   |
  |    34   |   R001   |      2      |      2    |   6000   |
  |    36   |   R001   |      2      |      3    |   2000   |
  +---------+----------+-------------+-----------+----------+
  </code></pre>
  
  <p>and i want result like this when i select t_prod_lot = R001. count t_prod_sublot when same value and sum t_prod_qty when t_prod_sublot same value</p>
  
  <pre><code>+---------+----------+-------------+-----------+----------+--------------------+---------------+
  |t_prod_id|t_prod_lot|t_prod_sublot|t_prod_card|t_prod_qty|count(t_prod_sublot)|sum(t_prod_qty)|
  +---------+----------+-------------+-----------+----------+--------------------+---------------+
  |    4    |   R001   |      1      |      1    |   6000   |          6         |      36000    |
  |    5    |   R001   |      1      |      2    |   6000   |          6         |      36000    |
  |    6    |   R001   |      1      |      3    |   6000   |          6         |      36000    |  
  |    10   |   R001   |      1      |      4    |   6000   |          6         |      36000    |  
  |    11   |   R001   |      1      |      5    |   6000   |          6         |      36000    |  
  |    12   |   R001   |      1      |      6    |   6000   |          6         |      36000    |  
  |    13   |   R001   |      2      |      1    |   6000   |          3         |      14000    |  
  |    34   |   R001   |      2      |      2    |   6000   |          3         |      14000    |  
  |    36   |   R001   |      2      |      3    |   2000   |          3         |      14000    |  
  +---------+----------+-------------+-----------+----------+--------------------+---------------+
  </code></pre>
  
  <p>what is the query to produce this result ?
  <a href="http://sqlfiddle.com/#!9/9189e" rel="nofollow">Sql Fiddle</a></p>