MySQL MAX from SUM

Question

This is freaking me out! Got the following data:

+----+-----+-------+------------+
| 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 |
+----+-----+-------+------------+

PS {edit 0001} :: there's an extra field, which also must be used for filter data, call it {TYPE}, an could get 'SINGLE' or 'MULTIPLE' as value.

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.

That will be, 1st step get the SUM for each {REG}:

+----+------+
| ID | SUM  |
+----+------+
| 1  | 300  |
| 2  | 300  |
| 2  | 400  |
+----+------+

That's:

SELECT 
  SUM(value) 
FROM 
  table 
WHERE
  (date BETWEEN '2009-01-01' AND '2009-03-01')
GROUP BY
  reg;

And then, get the MAX from each SUM, which is where I'm stucked:

+----+------+
| ID | MAX  |
+----+------+
| 1  | 300  |
| 2  | 400  |
+----+------+

I've tried:

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);

Any idea? PS: Sorry for mistakes.

PS {edit 0002} Gonna copy original queries and data, so may it helps better.

$QUERY:

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;

Got:

+---------+-------------+
| CLIENTE | SUMA        |
+---------+-------------+
| 64      | 1380690.74  |
| 187     | 1828468.71  |
| 187     | 2828102.80  |
| 325     | 26037422.21 |
| 389     | 875519.05   |
| 495     | 20084.93    |
| 495     | 109850.46   |
+---------+-------------+

Then, what I'm looking for is:

+---------+-------------+
| CLIENTE | MAX         |
+---------+-------------+
| 64      | 1380690.74  |
| 187     | 1828468.71  |
| 325     | 26037422.21 |
| 389     | 875519.05   |
| 495     | 109850.46   |
+---------+-------------+

But running:

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;

Results as:

+---------+-------------+
| CLIENTE | SUMA        |
+---------+-------------+
| 64      | 336879.21   |
| 187     | 1232824.51  |
| 325     | 3816173.62  |
| 389     | 218423.83   |
| 495     | 34105.99    |
+---------+-------------+

did you read about having ??

7alhashmi
– 7alhashmi

2013-08-26 09:43:25 +00:00
Commented Aug 26, 2013 at 9:43 — 7alhashmi
– 7alhashmi, Commented Aug 26, 2013 at 9:43
consider providing a sqlfiddle

Strawberry
– Strawberry

2013-08-26 11:23:45 +00:00
Commented Aug 26, 2013 at 11:23 — Strawberry
– Strawberry, Commented Aug 26, 2013 at 11:23

bvr · Accepted Answer · 2013-08-26 09:58:49Z

15

SELECT ID, MAX(reg_sum)
FROM
(
   SELECT ID, SUM(value) AS reg_sum FROM table 
   WHERE  (date BETWEEN '2009-01-01' AND '2009-03-01')
   GROUP BY  ID, reg
) a GROUP by ID

edited Aug 26, 2013 at 9:58

bvr

4,8361 gold badge23 silver badges24 bronze badges

answered Aug 26, 2013 at 9:47

DB_learner

1,0369 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Wolfchamane Over a year ago

If I run this query, seems that every single calculation is wrong.

DB_learner Over a year ago

Can you narrate what calculation is wrong, give example data.

Wolfchamane Over a year ago

Examples @ original post

DB_learner Over a year ago

It looks like you have same nuc shared across multiple clientid. Try to do a select without aggregation, based on that decide what you want in result. I have aggregated based on both columns, if you want to aggregate on only one column, change accordingly. But i prefer aggregation based on both columns which will give correct result.

Wolfchamane Over a year ago

Can you give me an example?

|

Ahmad Alhusainy · Accepted Answer · 2017-11-12 10:11:15Z

4

You can add [ order by SUM(value) DESC limit 1 ] to get the maximum value of the query results.

 SELECT  SUM(value) as maxcount  FROM table WHERE (date BETWEEN '2009 01-01' AND '2009-03-01') GROUP BY reg  order by maxcount desc  limit 1;

edited Nov 12, 2017 at 10:11

answered Nov 12, 2017 at 10:05

Ahmad Alhusainy

546 bronze badges

1 Comment

West Side Over a year ago

What u wanna do if was found two clients with equals sum?

Collectives™ on Stack Overflow

MySQL MAX from SUM

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related