CODEWITHSUNDEEP
mysqlsqlrow
rocky a
rocky a

Reputation: 144

SQL Row wise total value

I have a table named calcu

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

This is my table in SQL Fiddle

I need row wise total value. I means in a new column total, it will be (s1 + s2 + s3 + s4) i.e. (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.

Result will be like below:

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

see my problem here

It results all total 161 and 3 rows become 1 row.

How to write SQL query?

Upvotes: 1

Views: 1996

Answers (3)

user3645023
user3645023

Reputation: 1

SELECT *,s1+s2+s3+s4 as Total FROM calcu

Upvotes: 0

Valli
Valli

Reputation: 1450

select  c.id, 
       c.date, c.name, c.s1, c.s2, c.s3, c.s4, 
       least(s1,s2,s3,s4) Minvalue, 
       (s1+s2+s3+s4) Total
from calcu c

I tried simplifying the query. So you are looking for the minimum value among s1,s2,s3 and s4. You can achieve with least function. And you need a total of all four 's' columns. Just add them

Upvotes: 1

Serge
Serge

Reputation: 4036

The SUM() function is an aggregate function. As with other aggregates, use it only to compute values across multiple rows.

You want to add up values in one row, so just use the + operator (brackets are optional).

As for finding the minimum value in the row, use CASE WHEN with 3 tests, comparing S1, S2, S3 and S4.

This should work:

select 
  c.id, c.date, c.name, c.s1, c.s2, c.s3, c.s4, 
  (c.s1 + c.s2 + c.s3 + c.s4) as total,
  case 
    when c.s1 <= c.s2 and c.s1 <= c.s3 and c.s1 <= c.s4 then c.s1
    when c.s2 <= c.s1 and c.s2 <= c.s3 and c.s2 <= c.s4 then c.s2
    when c.s3 <= c.s2 and c.s3 <= c.s1 and c.s3 <= c.s4 then c.s3
    when c.s4 <= c.s2 and c.s4 <= c.s3 and c.s4 <= c.s1 then c.s4
  end as min_value
from calcu c
;

See SQLFiddle

Upvotes: 1

Related Questions