Subtract Values from Two Columns in SQL Query

Subtract values from two columns in sql query

You need to use group by but also you probably just want to sum the payments and subtract those against the single total bill.

SELECT bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT] 
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn
WHERE bill_record.PKColumn = @billId
GROUP BY bill_record.total_bill

Note that the group by here works because you are also filtering in the WHERE clause. If you want to get the results for multiple bills you would also group by a unique bill identifier and have it be returned in the SELECT.

SELECT bill_record.PKColumn AS BillId, bill_record.total_bill - SUM(invoice_payments.payment) AS [LEFT AMOUNT] 
FROM bill_record INNER JOIN invoice_payments ON bill_record.PKColumn = invoice_payments.FKColumn
GROUP BY bill_record.PKColumn, bill_record.total_bill

How to subtract 2 values from 2 different columns of the same table with different WHERE conditions?

Try this. You can now easily calculate the diff between two SUM result.

SELECT 
SUM(
CASE
WHEN ticketState IN ('STAND BY' , 'WIN' , 'LOSE') THEN ticketTotalAmount
ELSE 0
END
) AS TotalAmount,
SUM(
CASE
WHEN ticketState = 'WIN' THEN ticketTotalWin
ELSE 0
END
) AS TotalWin
FROM tickets
WHERE ticketDate > DATEADD(hh,-1,GETDATE()) AND ticketDate < GETDATE()

For getting differences between two sum, just use this following query which will return a single value. You can use this for insert purpose as well.

SELECT 
SUM(
CASE
WHEN ticketState IN ('STAND BY' , 'WIN' , 'LOSE') THEN ticketTotalAmount
ELSE 0
END
) -
SUM(
CASE
WHEN ticketState = 'WIN' THEN ticketTotalWin
ELSE 0
END
) AS TotalDifference
FROM tickets
WHERE ticketDate > DATEADD(hh,-1,GETDATE()) AND ticketDate < GETDATE()

Subtract two columns from two different tables successively

You're looking for the "AS" clause with some math thrown in.

You're also not giving it any columns to match, which means that ALL the rows from BOTH tables are just going to be returned as is.

You can fix this by giving it a specific column to match.

Like so:

SELECT 
twenty_seller.totalCost AS 'cost1',
twentyBuyer.totalCost AS 'cost2',
twenty_seller.totalCost-twentyBuyer.totalCost AS 'Difference'
FROM twenty_seller
LEFT OUTER JOIN twentyBuyer ON twenty_seller.ID=twentyBuyer.ID;

Or, if you just want the difference:

SELECT 
twenty_seller.totalCost-twentyBuyer.totalCost AS 'Difference'
FROM twenty_seller
LEFT OUTER JOIN twentyBuyer ON twenty_seller.ID=twentyBuyer.ID;

And if you want the total over all the rows:

SELECT 
SUM(twenty_seller.totalCost)-SUM(twentyBuyer.totalCost) AS 'Difference'
FROM twenty_seller
LEFT OUTER JOIN twentyBuyer ON twenty_seller.ID=twentyBuyer.ID;

SQL: Subtract two columns

Just add:

SUM(case when price >= 0 THEN 1 ELSE 0 END) - 
SUM(case when price < 0 THEN 1 ELSE 0 END) AS NetCount

as your last statement, so you'd end up with this:

select content_type_code_id 
, ABS(price) AS price
, SUM(case when price >= 0 THEN 1 ELSE 0 END) AS debits
, SUM(case when price < 0 THEN 1 ELSE 0 END) AS credits
, SUM(case when price >= 0 THEN 1 ELSE 0 END) -
SUM(case when price < 0 THEN 1 ELSE 0 END) AS NetCount
from dbo.transaction_unrated
where transaction_date >= '2012/05/01'
and transaction_date < '2012/06/01'
and content_provider_code_id in (1)
group by content_type_code_id, ABS(price)
ORDER BY ABS(price) ASC

Derived table version for Lamak:

You can also use a derived table to make the code a little cleaner:

select content_type_code_id,
price, debits, credits, (debits - credits) as NetCount
from (
select content_type_code_id
, ABS(price) AS price
, SUM(case when price >= 0 THEN 1 ELSE 0 END) AS debits
, SUM(case when price < 0 THEN 1 ELSE 0 END) AS credits
from dbo.transaction_unrated
where transaction_date >= '2012/05/01'
and transaction_date < '2012/06/01'
and content_provider_code_id in (1)
group by content_type_code_id, ABS(price)
) YourDerivedTable
ORDER BY price ASC

Subtracting two columns within the sql query

You can do it without a CTE by repeating the entire formula that makes up AllocatedToDate.

You cannot use the alias of a column in the SELECT list, so you cannot do this:

SELECT {some calculation} AS ColumnA, (ColumnA - ColumnB) AS ColumnC

If you don't want to use a CTE or derived table, you have to do this:

SELECT {some calculation} AS ColumnA, ({some calculation} - ColumnB) AS ColumnC

And by the way, I can't imagine why the possibility of future columns being added is a reason not to use a CTE. To me, it sounds like a reason TO use a CTE, as you will only have to make changes in one place in the code, and not duplicate the same code in different places in the same query.

Subtract two columns from two tables with Group By

  • For each individual Select query results, get an additional field, namely, factor. Its value will be +1 for stock in, and -1 for stockout.
  • Combine the results of individual select queries using Union All, and utilize the resultset as a Derived Table.
  • Now, simply do a Sum again, multiplying with the factor, on a grouping of BranchId.

Try the following query:

SELECT derived_t.BranchId, 
SUM(derived_t.factor * derived_t.quantity) AS Quantity
FROM
(
select BranchId,
ifnull(sum(Quantity),0) as quantity,
1 as factor
from stockin
where productid=1
group by BranchId

UNION ALL

select BranchId,
ifnull(sum(Quantity),0) Quantity,
-1 as factor
from stockout
where productid=1
group by BranchId
) AS derived_t

GROUP BY derived_t.BranchId

Subtract row values of 2 columns within same table in SQL query

Try this:

SELECT ID, (COM - REFUND) AS OUTPUT
FROM TABLE_NAME
WHERE SUPPORT IS NOT NULL AND STATUS = 1;

The 'as output' names the column in your result set. The where statement filters out your rows by the conditions you have given. The FROM clause tells on which table to execute the query. You have to put your own table name.



Related Topics



Leave a reply



Submit