What's the Best way to select the min value from several columns and then calculate their sum?

Question

i'm trying to: 1) Select the min value between col_1, col_2, and col_3 2) Calculate the sum of the minumum values based on the id_col value.

Using the example table below, the result I'm expecting is:

+--------+--------------+
| id_col | sum          | 
+--------+--------------+
|    123 | 523.99996667 |
+--------+--------------+

example_table

+--------+--------------+----------+---------+------+
| id_col | col_1        | col_2    | col_3   | id   |
+--------+--------------+----------+---------+------+
|    123 | 175.00000000 | 150.0000 |    NULL | 999  |
|    123 | 175.00000000 | 150.0000 |    NULL | 999  |
|    123 | 175.00000000 | 150.0000 |    NULL | 999  |
|    123 |  41.66666667 |  50.0000 |    NULL | 4444 |
|    123 |  50.00000000 | 100.0000 | 32.3333 | 5555 |
+--------+--------------+----------+---------+------+

I've tried the below to select the min value between the 3 columns, but it's only selecting the min value within the entire table instead.

select id_col,
SUM(CASE WHEN col_1 < col_2 AND col_1 < col_3 THEN col_1
            WHEN col_2 < col_1 AND col_2 < col_3 THEN col_2
            ELSE col_3 END) sum
from example_table
group by 1```

Your current query looks correct to me, and given that there is only one `id_col` group, the entire table and the group means the same thing. What is wrong with your query? — Tim Biegeleisen, May 09 '19 at 05:08
Details discussion on similar question will be found [Here](https://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-several-columns). — mkRabbani, May 09 '19 at 05:09
@TimBiegeleisen - I want to select the min value in each row with the same `id_col` and then add them together. So the sum i'm looking to generate is `523.99996667`. My query above generates `32.3333` as the sum. — Cookie Monster, May 09 '19 at 05:23
This kind of problem can be symptomatic of poor design - just sayin' — Strawberry, May 09 '19 at 06:58

Fahmi · Answer 1 · 2019-05-09T05:14:07.317

If your dbms is mysql then you can use least()

select id_col,SUM(least(coalesce(col1,0),coalesce(col2,0),coalesce(col3,0)))
from tablename
group by id_col

OR

select id_col,
SUM(CASE WHEN coalesce(col_1,0) < coalesce(col_2,0) AND coalesce(col_1,0) < coalesce(col_3,0) THEN col_1
            WHEN oalesce(col_2,0) < oalesce(col_1,0) AND oalesce(col_2,0) < oalesce(col_3,0) THEN col_2
            ELSE oalesce(col_3,0) END) sum
from example_table
group by 1

score 1 · Answer 2 · answered May 09 '19 at 05:29

Just an alternative to the @fa06 answer (+1), you could first use a subquery to coalesce all NULL values to zero, and then just use your current query verbatim:

WITH cte AS (
    SELECT
        id,
        id_col,
        COALECSE(col_1, 0) AS col_1,
        COALECSE(col_2, 0) AS col_2,
        COALECSE(col_3, 0) AS col_3
    FROM example_table
)

SELECT
    id_col,
    SUM(CASE WHEN col_1 < col_2 AND col_1 < col_3 THEN col_1
             WHEN col_2 < col_1 AND col_2 < col_3 THEN col_2
             ELSE col_3 END) AS sum
FROM cte
GROUP BY id_col;

score 1 · Answer 3 · answered May 09 '19 at 05:38

1

For SQL Server, This following script will work. You can add as many column you need in the CROSS APPLY list

SELECT B.ID [id_col],SUM(B.least) [SUM]
FROM
(
    SELECT  A.ID,A.RN, MIN(T.CombinedValue) AS least FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY yt.ID ORDER BY ID) RN, * 
        FROM your_table yt
    ) A
    CROSS APPLY ( VALUES ( A.col1 ), ( A.col2 ), ( A.col3 )) AS T (CombinedValue)
    GROUP BY A.ID,A.RN
)B
GROUP BY B.ID

answered May 09 '19 at 05:38

mkRabbani

16,295
2
15
24

1

The OP already stated in comments that it is using MySQL 8, _not_ SQL Server. – Tim Biegeleisen May 09 '19 at 06:42

What's the Best way to select the min value from several columns and then calculate their sum?

3 Answers3