How to 'Subtract' SQL Tables

How do I 'subtract' sql tables?

The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT

  SELECT ... // all documents
  EXCEPT
  SELECT ... // active documents

I believe that the EXCEPT set operation became available in SQL Server 2005.

sql - subtract values from different tables

You can directly perform operations on multiple columns and show that result in a new column which you can name using as clause. You can perform join to do operation between two columns of different tables in a similar fashion. To answer your question, following is the SQL query:

SELECT a.id, a.amount-b.amount as Difference FROM table1 a
INNER JOIN table2 b
ON a.id=b.id

How to subtract values generated from two tables?

You can use cross join:

select n.nov_sum  - o.oct_sum as REVENUE_DIFF
from (select sum(n.price) as nov_sum
      from nov_data_part n
      where event_type = 'purchase'
     ) n cross join
     (select sum(o.price) as oct_sum
      from oct_data_part n
      where event_type = 'purchase'
     ) o;

As a note: You have a real problem with your data model if you are splitting rows across different tables based on the month.

Subtraction value with same table

I am going to assume that you have another column which tells you the order in which the qty should be subtracted from the order column and have called this the datetime column (but you can use whatever column you have available to provide a deterministic ordering of the rows). Given that, you can calculate the value using an analytic SUM function:

SELECT id,
       cust,
       datetime,
       order_total,
       qty,
       order_total
         - SUM( COALESCE( qty, 0 ) )
             OVER ( PARTITION BY id, cust, order_total ORDER BY datetime )
         AS value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( id, cust, datetime, order_total, qty ) AS
SELECT '0001',  'abc',  DATE '2021-01-01', 10000,   2000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-02', 10000,   4000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-03', 10000,   4000 FROM DUAL UNION ALL
SELECT '0002',  'bcd',  DATE '2021-01-01', 5000,    2000 FROM DUAL UNION ALL
SELECT '0003',  'fgh',  DATE '2021-01-01', 3000,    1000 FROM DUAL UNION ALL
SELECT '0004',  'ghj',  DATE '2021-01-01', 5000,    NULL FROM DUAL;

Outputs:

ID   | CUST | DATETIME  | ORDER_TOTAL |  QTY | VALUE
:--- | :--- | :-------- | ----------: | ---: | ----:
0001 | abc  | 01-JAN-21 |       10000 | 2000 |  8000
0001 | abc  | 02-JAN-21 |       10000 | 4000 |  4000
0001 | abc  | 03-JAN-21 |       10000 | 4000 |     0
0002 | bcd  | 01-JAN-21 |        5000 | 2000 |  3000
0003 | fgh  | 01-JAN-21 |        3000 | 1000 |  2000
0004 | ghj  | 01-JAN-21 |        5000 | null |  5000

db<>fiddle here

How to subtract values from different tables?

You can do this with a CTE or with subqueries.

Using a CTE:

WITH tbla AS (
    SELECT COUNT(A.Id) A
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
),
tblb AS (
    SELECT COUNT(B.Id) B
    FROM Table_B AS B
    WHERE B.City = 'England'
),
tblc AS (
    SELECT COUNT(C.Id) C
    FROM Table_C AS C
    WHERE C.City = 'Berlin'
)
SELECT a.A - b.B - c.C
FROM tbla a
CROSS JOIN tblb b
CROSS JOIN tblc c;

Same thing only with subqueries

SELECT a.A - b.B - c.C
FROM (
    SELECT COUNT(A.Id) A
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
) a
CROSS JOIN (
    SELECT COUNT(B.Id) B
    FROM Table_B AS B
    WHERE B.City = 'England'
) b
CROSS JOIN (
    SELECT COUNT(C.Id) C
    FROM Table_C AS C
    WHERE C.City = 'Berlin'
) c;

Note: I removed the WITH (NOLOCK) hints because in all likelihood they are not needed in this case.

Subtract one table from another

Basically FYI If A={A,A,O},B={A,P} then A-B is logically

select * from t1 except select * from t2

try this !

create table #t(id varchar(10))
create table #t1(id1 varchar(10))
insert into #t values('apple'),('apple'),('orange')
insert into #t1 values('apple'),('pear')

select * from
(
select *,rn=row_number()over(partition by id order by id) from #t
except
select *,rn1=row_number()over(partition by id1 order by id1) from #t1
)x

SEE DEMO

SQL Subtract two columns on different tables

Use a derived table for the invoice amount SUM(), then JOIN back to Customer:

DECLARE @CreditRemaining INT

SELECT @CreditRemaining = (c.CreditLimit - TotalSpent)
FROM Customer c
INNER JOIN (SELECT SUM(Amount) TotalSpent, CustomerID
            FROM Invoices
            GROUP BY CustomerID) i ON i.CustomerID = c.ID

As others mentioned, this is assuming you are limiting your selection to one customer.

For all customers, just use a select:

SELECT C.Name, (c.CreditLimit - TotalSpent) CreditRemaining
FROM Customer c
INNER JOIN (SELECT SUM(Amount) TotalSpent, CustomerID
            FROM Invoices
            GROUP BY CustomerID) i ON i.CustomerID = c.ID
GROUP BY C.Name

Subtraction of counts of 2 tables

The potential issue here is that for any week you might only have additions or removals, so to align a count from the 2 tables - by week - an approach would be to use a full outer join, like this:

SELECT COALESECE(A.week, b.week) as week
     , count_a
     , count_b
     , COALESECE(count_a,0) - COALESECE(count_b,0) net
FROM (
    SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS week
         , Count(*) AS count_A
    FROM table_a
    GROUP BY DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08')
    ) a
FUUL OUTER JOIN (
    SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS week
         , Count(*) AS count_b
    FROM table_b
    GROUP BY DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08')
    ) b on a.week = b.week

---

Related Topics