Query to find the additional value in a table based on condition in MySQL-CodePudding

I have a table below

Id	group	value
1	A	11
2	A	3
2	B	2
2	C	3
2	D	2
3	D	8
4	A	6
4	C	6

I would like to display the result

Id	value	Additional Value
1	8	3
2	8	2
3	8	0
4	8	4

MySQL Query

SELECT id , 
       SUM(VALUE), 
       SUM( CASE WHEN (day_type = 'R' AND (VALUE) > 8) THEN (VALUE - 8) WHEN (day_type = 'R' AND (VALUE) <= 8) THEN 0 ELSE 0 END) AS additional_value 
FROM TABLE
GROUP BY id

I would like to split the value, into value and additional value, if the value is> 8 I need to add the extra value in the additional value column, My query works for a single row with a value > 8 but with multiple combinations, it does not work.

Could anyone give me a solution for this? Thanks for the help.

CodePudding user response：

Use the functions LEAST() and GREATEST() to compare the total of value for each id to 8:

SELECT id, 
       LEAST(SUM(value), 8) value,
       GREATEST(SUM(value) - 8, 0) additional_value
FROM tablename
GROUP BY id;

See the demo.

CodePudding user response：

You can use LEAST() and GREATEST(). For example:

select
  id, 
  least(sum(value), 8) as value,
  greatest(0, sum(value) - 8) as additional_value
from t
group by id