Have an upper limit for SUM() function sql

Question

I'm doing a small calculation in the select statement of an sql query along the lines of this:

SELECT 
   (select sum(weighting) 
     from table1 
     where id = tablemain.id) * 
   (select sum(weighting) 
     from table2 
     where id = tablemain.id) as rating
from 
  tablemain

The problem I have is that one of these SUM values should have an upper limit of 8, e.g. if the SUM of the weight columns comes to 10 it should only multiply by 8.

Does anybody have any ideas of how this could be done in the select statement or if it's even feasible?

Thanks

possible duplicate of [Getting the minimum of two values in sql](http://stackoverflow.com/questions/1947753/), [What's the best way to select the minimum value from multiple columns?](http://stackoverflow.com/questions/368351/), [Is there a Max function in SQL Server that takes two values like Math.Max in .NET?](http://stackoverflow.com/questions/124417/). — outis, Dec 07 '11 at 15:06
Added an alternative that should be faster, still using CASE WHEN THEN but avoiding the two correlated sub-queries. — MatBailie, Dec 07 '11 at 16:08

score 4 · Accepted Answer · answered Dec 07 '11 at 15:05

You might try a case statement. I would even select the values in a CTE to simplify the code, but the principle is the same.

SELECT  
   (select CASE WHEN sum(weighting) > 8 THEN 8 ELSE sum(weighting) END 
     from table1  
     where id = tablemain.id) *  
   (select CASE WHEN sum(weighting) > 8 THEN 8 ELSE sum(weighting) END 
     from table2  
     where id = tablemain.id) as rating 
from  
  tablemain

icyrock.com · Answer 2 · 2011-12-07T15:13:02.093

1

Note: Thanks to Leons for pointing this out, the below does not work, as min takes one parameter only per http://msdn.microsoft.com/en-us/library/ms179916.aspx:

select min(8, sum(weighting)) ...

edited Dec 07 '11 at 15:13

answered Dec 07 '11 at 15:01

icyrock.com

27,952
4
66
85

1

The MIN function in T-SQL does not take 2 parameters – Leons Dec 07 '11 at 15:04
Doh! Yep, agree: http://msdn.microsoft.com/en-us/library/ms179916.aspx. I'll edit for future. Thanks! – icyrock.com Dec 07 '11 at 15:10

soulcheck · Answer 3 · 2011-12-07T16:27:08.550

1

Be careful with your query. Right now it will do 2 correlated subqueries for every row in tablemain. Another way to do it would be to use joins and group by. Test which is faster in your case.

SELECT id, 
 (CASE WHEN t1.w > 8 THEN 8 ELSE t1.w END) *
 (CASE WHEN t2.w > 8 THEN 8 ELSE t2.w END) rating
FROM tablemain as t, (select id, sum(weighting) w  from table1 group by id) as t1, (select id, sum(weighting) w from table2 group by id) as t2
WHERE t.id = t1.id and t.id = t2.id

edited Dec 07 '11 at 16:27

answered Dec 07 '11 at 15:28

soulcheck

36,297
6
91
90

If either of these relationships are 1:many (which is almost certain considering the use of SUM) then this query does *not* provide the same functionality as the OP's example. `main(id)={1}` & `t1(id,w) = {(1,3) (1,6)}` & `t2(id,w) = {(1,4) (1,2) (1,1)}` : SUM(t1.w) should be 9, yours gives 27. SUM(t2.w) should be 7, yours give 14. [So, yes, be careful with your queries.] – MatBailie Dec 07 '11 at 16:03
you're right, it was a last minute edit where i tried to outsmart myself. corrected. – soulcheck Dec 07 '11 at 16:20
@Dems damn, didn't notice that your solution is esencially the same. Have an upvote! – soulcheck Dec 07 '11 at 16:38

score 1 · Answer 4 · answered Dec 07 '11 at 16:07

An alternative to avoid correlated sub-queries without causing massive duplication...

SELECT id, 
 (CASE WHEN (t1.weight > 8) THEN 8 ELSE t1.weight END) *
 (CASE WHEN (t2.weight > 8) THEN 8 ELSE t2.weight END)             AS rating
FROM
  tablemain                                                        AS t
LEFT JOIN
  (SELECT id, SUM(weighting) AS weight FROM table1 GROUP BY id)    AS t1
    ON t1.id = t.id
LEFT JOIN
  (SELECT id, SUM(weighting) AS weight FROM table2 GROUP BY id)    AS t2
    ON t2.id = t.id

Have an upper limit for SUM() function sql

4 Answers4