CODEWITHSUNDEEP
mysqlsql
Kris
Kris

Reputation: 33

How to do subtraction between two fields in a Table using an SQL query

I want to display the subtraction of two values from two different columns to a third column using a SQL query.

This is the table structure:

------------------------------------
id | subject | Time1 | Time2| Time3
------------------------------------
1  | String1 | 50    |  78  | 
2  | String2 | 60    |  99  |  
3  |         |       |      |

I want to subtract Time2 from Time1 if both Time1 and Time2 has data and store in Time3.

Output should be like as

------------------------------------
id | subject | Time1 | Time2| Time3
------------------------------------
1  | String1 | 50    |  78  |  28
2  | String2 | 60    |  99  |  39
3  |         |       |      |

Thanks!

Upvotes: 1

Views: 15991

Answers (8)

CMK
CMK

Reputation: 11

Try this

SELECT id, subject, Time1, Time2, CONCAT(Time2 - Time1) AS Time3 FROM tabel

Upvotes: 0

amtg
amtg

Reputation: 124

If Time1 and Time2 are datetime fields, then you should use TIMEDIFF function in MySQL to be able to correctly format the Time3 field.

SELECT id, subject, Time1, Time2, TIMEDIFF(Time1, Time2) as Time3 
  FROM tbl

http://www.w3resource.com/mysql/date-and-time-functions/mysql-timediff-function.php

Upvotes: 0

Chirag paneliya
Chirag paneliya

Reputation: 31

`SELECT 
     id, 
     subject, 
     Time1, 
     Time2, 
     IF( Time1 != '' AND Time2 != '', Time2-Time1, '') as Time3 
FROM tbl_time`

Upvotes: 2

Bahadur Singh Deol
Bahadur Singh Deol

Reputation: 821

Try this

SELECT *, `Time2`-`Time1` AS `Time3` FROM `tablename`

Upvotes: 2

Ilario Pierbattista
Ilario Pierbattista

Reputation: 3265

This checks the existence of data in Time1 and Time2: if there is enough data, Time3 = Time2 - Time1; otherwise it's null

select id, subject, Time1, Time2, 
   ifnull(Time1, null, ifnull(Time2, null, Time2 - Time1)) as Time3 
from myTable

Upvotes: 0

Vecchiasignora
Vecchiasignora

Reputation: 1315

just try this

select id, subject, Time1, Time2, Time2-Time1 as Time3  from mytable

Upvotes: 1

Koby Douek
Koby Douek

Reputation: 16675

Assuming Time1 and Time2 are Number columns, you can use a simple subtraction:

select id, subject, Time1, Time3, Time2 - Time1 as Time3 from myTable

Upvotes: 1

Tom
Tom

Reputation: 9878

SELECT
    id
    ,subject
    ,Time1
    ,Time2
    ,Time2-Time1 as Time3
FROM tbl

Upvotes: 1

Related Questions