CODEWITHSUNDEEP
mysql
jscul
jscul

Reputation: 772

I can't seem to use SUM(*) and MAX(*) in the same query

What I'm trying to do is have essentially a 3 column table based off two columns. 

id, value
----------  
1,  7
1,  1 
2,  6 
3,  9
3,  2
4,  2

I want [below] from [above]...

 id, sum_of_strength, max_of_strength
 ---------------------------------------
  1, 8,               11
  2, 6,               11 
  3, 11,              11
  4, 2,               11

Here's what I'm playing around with currently, but the max_value is the same as the sum_value... Does anyone know if there's a way to do this. I've tried loads of other solutions but nothing has worked.

SELECT 
    sum_table.id,
    sum_table.sum_value,
    (SELECT MAX(sum_table.sum_value) AS max_value) AS max_value
FROM (
    SELECT
        original_table.id,
        CAST(SUM(original_table.value) AS UNSIGNED) AS sum_value
    FROM original_table
    GROUP BY original_table.id
) AS sum_table
GROUP BY sum_table.id;

Upvotes: 1

Views: 124

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use a subquery to get the max value:

SELECT id,
       CAST(SUM(value) AS UNSIGNED) AS sum_value,
       (SELECT CAST(SUM(value) AS UNSIGNED) AS s
        FROM original_table
        GROUP BY id
        ORDER BY s DESC 
        LIMIT 1) AS max_value
FROM original_table
GROUP BY id

Grouping has to be applied twice:

  • The main query uses it to get the sum of value per id
  • The subquery uses it to get the maximum sum among all ids

Demo here

Upvotes: 2

Related Questions