Minimum Value column calculation by grouping total in sql

Question

I have a table named calcu

id  date       name     s1       s2      s3     s4       min_value
1   2/10/2017  dicky    7        4       8      9       [4]
2   2/10/2017  acton    12      15       17     19      [15]
3   2/10/2017  adney    28      13       19     10      [13]

If I total all fields in a single date then the result will be

    2/10/2017           47      32      44      38

Here minimum value is 32. It means minimum value column is s2. That's why I input s2 field value in min_value field in table calcu respectively.

I need how will I complete min_value field through SQL query?

I am using MYSQL database.

Plz show SQL query and query result.

pls. tag the DBMS (MySQL, MS SQL Server, Oracle, etc ..) which u r using. — Yogesh Sharma
– Yogesh Sharma, Commented Oct 10, 2017 at 4:35
You can see this ... It can be helpful for you. stackoverflow.com/questions/10742616/… — Rintu Kumar Chowdhury
– Rintu Kumar Chowdhury, Commented Oct 10, 2017 at 4:42

Thorsten Kettner · Accepted Answer · 2017-10-13 05:47:37Z

0

You want to find the column with the minimum sum first and then use that column in your query. So you need a subquery where you compare each sum with the others and remember the column name. Then in the main query use that column name to check which value to show.

select 
  c.id, c.date, c.name, c.s1, c.s2, c.s3, c.s4, 
  case 
    when col.colname = 's1' then c.s1
    when col.colname = 's2' then c.s2
    when col.colname = 's3' then c.s3
    when col.colname = 's4' then c.s4
  end as min_value
from calcu c
cross join
(
  select
    case 
      when sum(s1) <= sum(s2) and sum(s1) <= sum(s3) and sum(s1) <= sum(s4) then 's1'
      when sum(s2) <= sum(s1) and sum(s2) <= sum(s3) and sum(s2) <= sum(s4) then 's2'
      when sum(s3) <= sum(s1) and sum(s3) <= sum(s2) and sum(s3) <= sum(s4) then 's3'
      else                                                                       's4'
    end as colname
  from calcu
) col;

The altered SQL fiddle: http://sqlfiddle.com/#!9/31579c/3

answered Oct 13, 2017 at 5:47

Thorsten Kettner

96.6k8 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rocky a Over a year ago

My problem has been solved. Now May I get row wise total value? I means in a new column total, it will be (7+4+8+9) = 28 where id=1, (12+15+17+19)=63 where id=2, (28+13+19+10)=70 where id=3 respectively.

Thorsten Kettner Over a year ago

Why have you put sum(c.s1 + c.s2 + c.s3 + c.s4)? It's simply c.s1 + c.s2 + c.s3 + c.s4 without sum, because you only want to add values in the row itself, not aggregate data over several rows. sqlfiddle.com/#!9/31579c/40

rocky a Over a year ago

I have understood my mistake. one more question if don't mind, Is it possible to set a column named min_total where value will be min_total*4 i.e. 4*4 = 16 where id is 1.

Thorsten Kettner Over a year ago

You want a factor of 4 vs. 1 in case of id = 1. Use another case expression for this and multiply: case when c.id = 1 then 4 else 1 end * case when col.colname ... end as min_value.

rocky a Over a year ago

Actually I do not understand how will I create the multiply case query.. for the min_total column.

|

Collectives™ on Stack Overflow

Minimum Value column calculation by grouping total in sql

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related