How do I subtract these two columns in SQL and make an extra column that shows the difference?

Question

Begin_Term	End_Term
2018	Current
-select-	Current
2015	2019
-select-	2018

I used using the case when SUM('End_Term') - SUM ('Begin_Term) then 1 else 0 end but it only shows 0's and 1's for the results. I'm also using DOMO so the SQL formatting is alot different.

Answer 1

Does selecting a new column that merely subtracts the two columns (e.g. SELECT 'End_Term' - 'Begin_Term' AS Diff) mostly meet your needs?

As comments indicated, 3 of the 4 sample rows you gave are not reasonable to expect to process, since the columns do not both contain numbers.

If you have special case strings like "Current" and "-select-" that you wish to convert to numbers, then I suggest you will want to do this first, and then do your math second.

For example:

SELECT CASE WHEN 'End_Term' = "Current" THEN year(CURDATE()) ELSE 'End_Term' as 'End_Term'

My understanding is that DOMO handles type casting / conversion transparently behind the scenes so there's no need to actually worry about whether the column itself is a varchar.

I'm not sure what "-select-" is meant to be. It seems to me like erroneous placeholder form data that shouldn't have ended up submitted or inserted into your database in the first place.