Query SQL to Subtract Two Fields

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

I NEED TO SUBTRACT TWO VALUES BASED ON TWO COLUMNS

Not really sure on what your data model is, but could you give this a try and let me know?

SELECT
    num_nota,
    des_item,
    SUM(qtd_lancamento - qtd_fardos) diff_sub
FROM
    yourtable        a
    LEFT JOIN yourothertable   b ON b.column = a.column
GROUP BY
    num_nota,
    des_item

SQL: How to subtract two fields belongs to same ID

Assuming you are only 2 entries for each Tx_ID, you can try this:

SELECT Tx_ID,SUBTRACT(MAX(Tx_Time),MIN(Tx_Time)) FROM Tx_Table GROUP BY Tx_ID HAVING count(*) > 1

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

Subtract Two columns off the condition of two other column values

A Simple Left join on first_name equal and last_name not equal will align the num field with a value, when matched, or null. You cast null's to 0 and do your math for each records so that those that meet the criteria will adjust.

SELECT  
    D1.first_name,
    D1.last_name,
    num = D1.num - ISNULL(D2.num,0)
FROM
    table_1 D1
    LEFT OUTER JOIN table_1 D2 ON D2.first_name = D1.first_name AND D1.last_name <> D2.last_name

How i can i subtract two count selected columns in the same query?

Next time, please provide us some sample data, and the tables, you can use SQL Fiddle to make it easier.

Check if this will suit your needs:

SELECT province_code,
       COUNT(labs.mfl_code) TotalLabs,
       COUNT(uploads.lab_code) Uploads,
       COUNT(labs.mfl_code) - COUNT(uploads.lab_code) Pending
FROM labs
LEFT join uploads ON uploads.lab_code = labs.mfl_code
GROUP BY province_code

SQl Fiddle example:
http://sqlfiddle.com/#!9/c689e7/1

If you found my answer useful, i would appreciate if you vote up and mark as accepted.

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 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;