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
Select Only Rows That Contain Only Alphanumeric Characters in MySQL
How to Find What Privileges a Group Has in Redshift
How to Store Multiple Values in Single Field in SQL Database
Truncate Seconds and Milliseconds in SQL
How to Remove Special Characters in Column With MySQL
How to Have a Default Parameter for a MySQL Stored Procedure
Calculating Percent Change Between Two Rows
Sql Query Is Taking More Than 4 Minutes
Removing Null Value in SQL Join and Union Operators in SQL
How to Select Only 1 Row from Oracle SQL
Sql Query to Select Million Records Quickly
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Oracle Sql, Concatenate Multiple Columns + Add Text
How to Directly View Blobs in MySQL Workbench
Sql: How to Get Both Match and Non-Match Records
How to Select Rows With Only Numeric Characters in Oracle SQL