title: MySQL MAX from SUM
body: <p>This is freaking me out!
  Got the following data:</p>
  
  <pre><code>+----+-----+-------+------------+
  | ID | REG | VALUE | DATE       |
  +----+-----+-------+------------+
  | 1  | 1A  | 100   | 2009-01-01 |
  | 1  | 1A  | 100   | 2009-02-01 |
  | 1  | 1A  | 100   | 2009-03-01 |
  | 2  | 1B  | 100   | 2009-01-01 |
  | 2  | 1B  | 100   | 2009-02-01 |
  | 2  | 1B  | 100   | 2009-03-01 |
  | 2  | 1C  | 100   | 2009-01-01 |
  | 2  | 1C  | 100   | 2009-02-01 |
  | 2  | 1C  | 200   | 2009-03-01 |
  +----+-----+-------+------------+
  </code></pre>
  
  <p><strong>PS {edit 0001}</strong> :: there's an extra field, which also must be used for filter data, call it {TYPE}, an could get 'SINGLE' or 'MULTIPLE' as value.</p>
  
  <p>I want to get the MAX between SUM(of each different {REG}) for every {ID}. Obviously, this is a simple representation, table got up to 64985928 registers and {DATE} is the filtering data.</p>
  
  <p>That will be, 1st step get the SUM for each {REG}:</p>
  
  <pre><code>+----+------+
  | ID | SUM  |
  +----+------+
  | 1  | 300  |
  | 2  | 300  |
  | 2  | 400  |
  +----+------+
  </code></pre>
  
  <p>That's: </p>
  
  <pre><code>SELECT 
    SUM(value) 
  FROM 
    table 
  WHERE
    (date BETWEEN '2009-01-01' AND '2009-03-01')
  GROUP BY
    reg;
  </code></pre>
  
  <p>And then, get the MAX from each SUM, which is where I'm stucked:</p>
  
  <pre><code>+----+------+
  | ID | MAX  |
  +----+------+
  | 1  | 300  |
  | 2  | 400  |
  +----+------+
  </code></pre>
  
  <p>I've tried:</p>
  
  <pre><code>SELECT
    a.id,
    MAX(b.sum)
  FROM
    table a,
    (SELECT 
       SUM(b.value) 
     FROM 
       table b 
     WHERE 
       (b.date BETWEEN '2009-01-01' AND '2009-03-01') AND (a.id = b.id)
     GROUP BY
       b.reg);
  </code></pre>
  
  <p>Any idea?
  PS: Sorry for mistakes.</p>
  
  <p><strong>PS {edit 0002}</strong> Gonna copy original queries and data, so may it helps better.</p>
  
  <p>$QUERY: </p>
  
  <pre><code>SELECT 
    clienteid AS "CLIENTE",
    SUM(saldo) AS "SUMA" 
  FROM
    etl.creditos
  WHERE
     (titularidad_tipo LIKE 'TITULAR')
  AND
     (mes_datos BETWEEN '2008-11-01' AND '2009-10-01')
  GROUP BY
    nuc 
  ORDER BY
    clienteid;
  </code></pre>
  
  <p>Got:</p>
  
  <pre><code>+---------+-------------+
  | CLIENTE | SUMA        |
  +---------+-------------+
  | 64      | 1380690.74  |
  | 187     | 1828468.71  |
  | 187     | 2828102.80  |
  | 325     | 26037422.21 |
  | 389     | 875519.05   |
  | 495     | 20084.93    |
  | 495     | 109850.46   |
  +---------+-------------+
  </code></pre>
  
  <p>Then, what I'm looking for is:</p>
  
  <pre><code>+---------+-------------+
  | CLIENTE | MAX         |
  +---------+-------------+
  | 64      | 1380690.74  |
  | 187     | 1828468.71  |
  | 325     | 26037422.21 |
  | 389     | 875519.05   |
  | 495     | 109850.46   |
  +---------+-------------+  
  </code></pre>
  
  <p>But running: </p>
  
  <pre><code>SELECT
      clienteid AS "CLIENTE",
      MAX(suma)
  FROM
      (SELECT clienteid, SUM(saldo) AS "suma" FROM etl.creditos
      WHERE (mes_datos BETWEEN '2009-08-01' AND '2009-10-01') AND (titularidad_tipo LIKE 'TITULAR')
      GROUP BY clienteid, nuc) AS sums
  GROUP BY
      clienteid
  ORDER BY
      clienteid;
  </code></pre>
  
  <p>Results as:</p>
  
  <pre><code>+---------+-------------+
  | CLIENTE | SUMA        |
  +---------+-------------+
  | 64      | 336879.21   |
  | 187     | 1232824.51  |
  | 325     | 3816173.62  |
  | 389     | 218423.83   |
  | 495     | 34105.99    |
  +---------+-------------+
  </code></pre>