Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I have these 2 columns.

code  | total ($)
GROSS | 2500
BASIC | 2000
NET   | 2250


I want to create a new column called "difference" from the subtraction.
the results I want to have is obtained by subtracting "GROSS" and "NET" which is from the same column.
Which results to be --> 2500 - 2250 = 250
Posted
Updated 1-Mar-21 19:01pm
v2

if there are two columns gross n net then
SQL
select GROSS,  NET, 'DIFFRENCE' = GROSS-NET From tableName

if record in different rows,
SQL
select SUM(GROSS) as GROSS, SUM(NET) as NET, 'DIFFERENCE' = SUM(GROSS)-SUM(NET) FROM
(
select Total as GROSS, 0 as NET From tableName where Code='GROSS'
union all
select 0 as GROSS, TOTAL as NET From tableName where Code='NET'
) as a

Happy Coding!
:)
   
SQL
SELECT
(SELECT t1.total FROM tablename t1 WHERE t1.code='GROSS') AS GROSS,
(SELECT t2.total FROM tablename t2 WHERE t2.code='NET') AS NET,
(SELECT t3.total FROM tablename t3 WHERE t3.code='GROSS') -
(SELECT t4.total FROM tablename t4 WHERE t4.code='NET')  AS difference
   
This is a horrible DB design. Having the same column store different values results in problems like this one. This actually looks to me like a problem you can fix with a pivot, or a CTE like this

SQL
with vals
as
(
select Total as GROSS, 0 as NET From tableName where Code='GROSS'
union all
select 0 as GROSS, TOTAL as NET From tableName where Code='NET'
)

select gross, net, gross - net as difference


you can use sum in the final SQL if there's many values, but if there is, and there's no third column, your DB has even more issues.
   
If I want to take value of only difference then what to do ?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900