SUM values with condition in a column with similar ID-CodePudding

My table as shown as below.

Sub_JS_Number   Weight1 Weight2 Months

2215566-002B    4.95    1.317   June
2215566-002B    4.95    2.54    June
2215567-001A    7.44    2.451   June
2215567-001A    7.44    7.44    June
2216084-001A    7.23    3.897   June
2216085-001A    7.44    3.774   June
2214911-001A    6.56    1.104   July
2214911-001A    6.56    1.42    May
2214911-001A    6.56    1.83    May

I would like to sum the value in 'Weight2' column with the same 'Sub_JS_Number' column and 'Month' column.

However, if the sum value of weight2 is more than weight1, the weight2 value will be the maximum amount of weight2 for the particular 'Sub_JS_Number' and 'Months'.

Example of result I want is:

Sub_JS_Number   Weight1 Weight2 Months

2215566-002B    4.95    3.857   June
2215567-001A    7.44    7.44    June
2216084-001A    7.23    3.897   June
2216085-001A    7.44    3.774   June
2214911-001A    6.56    1.104   July
2214911-001A    6.56    3.25    May

CodePudding user response：

You should be able to do it with a case-clause and a subquery like this:

Select Sub_JS_Number, weight1, 
  CASE WHEN sum(weight2) > weight1
    THEN (select max(t.weight2) from myTable t where t.Sub_JS_Number = tbl.Sub_JS_Number and t.Months = tbl.Months)
  ELSE sum(weight2)
  END AS weight2, 
  Months
From myTable tbl
Group By Sub_JS_Number, Months

Test at http://sqlfiddle.com/#!9/a5c33a/1

CodePudding user response：

so we're grouping by Months and Sub_JS_Number: but you don't mention situation when some of detail weight1 values are less than sum(weight2) and others don't - so I guess this is not possible in your data. Also, the expected results are always on Months and Sub_JS_Number level, weight1 seems to be constant in one month and Sub_JS_Number.
this would make it possible to simply calculate aggregates for: max(weight1), max(weight2) and sum(weight2) by Months Sub_JS_Number and then decide:

with agr1 as (
    select 
    Months, Sub_JS_Number
    , max(weight1) as max_weight1
    , sum(weight2) as sum_weight2
    , max(weight2) as max_weight2
    from TABLE1
    group by Months, Sub_JS_Number
)
select Months, Sub_JS_Number
, case when sum_weight2 > max_weight1 then max_weight2 else sum_weight2 end as result
from agr1

http://sqlfiddle.com/#!18/16346/2/0