Subtract Two Columns from Two Tables With Group By

subtract two column from different table and group by

Try with inner join

        SELECT sum(pur.psum) - sum(sca.ssum) as quantities, p.scale, st.name , st.id FROM
 stuff st INNER JOIN  
    (SELECT sum(p.quantity) as psum,p.scale,p.stuff_id  FROM purchase p GROUP BY p.scale,p.stuff_id) pur 
on pur.stuff_id = st.id  INNER JOIN 
    (SELECT sum(s.quantity) as ssum,s.scale,s.stuff_id  FROM scale s GROUP BY s.scale,s.stuff_id) sca
 on sca.scale=pur.scale and sca.stuff_id=pur.stuff_id  group by pur.scale, st.name ORDER BY st.name , pur.scale;

Joining two tables on multiple columns and subtracting the value field

I'd go for a GROUP BY instead of FULL OUTER JOIN.

UNION ALL the tables, use negative SUM values for TABLE_B. GROUP BY the result.

SELECT Foo, Bar, Baz, SUM(Units)
FROM
(
  SELECT Foo, Bar, Baz, Units
  FROM Table_A
  UNION ALL
  SELECT Foo, Bar, Baz, -Units
  FROM Table_B
) dt
GROUP BY Foo, Bar, Baz

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 between two queries with group by

You need a proper JOIN of the tables, GROUP BY item_name, and then conditional aggregation:

SELECT item_name, SUM(reopened = 0) - SUM(reopened != 0) AS Difference
FROM orders AS O INNER JOIN invoiced_positions AS IP  
ON IP.order_id = O.id     
GROUP BY item_name;    

I did not qualify the columns item_name and reopened with table aliases because it is not obvious where they belong, but you should do it.

Subtract two columns of different tables with different number of rows

This is tricky, because products could be in one table but not the other. One method uses union all and group by:

select product_id, sum(quantity)
from ((select e.product_id, quantity
       from entrance e
      ) union all
      (select b.product_id, - b.quantity
       from buying b
      )
     ) eb
group by product_id;

How to substract two sums of columns from two different joins in laravel?

Using multiple joins in query will result in multiple rows for example if one article has 2 rows from incoming table 2 or more related rows from outgoing table which will produce incorrect sum value. To handle such type of issue you can use sub clauses for your related tables (incoming and outgoing) and calculate sum for each article in their respective sub cause. After that join these sub clauses with your article table and subtract their quantity.

In plain SQL it would look like

select  a.id,
        a.name,
        coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity
from articles as a
left join (
    select article_id,sum(quantity) as quantity
    from article_incoming_document
    where   warehous = :warehouse
        and warehouse_type: warehouse_type
    group by article_id
) i on a.id = i.article_id
left join (
    select article_id,sum(quantity) as quantity
    from article_outgoing_document
    where   warehous = :warehouse
        and warehouse_type: warehouse_type
    group by article_id
) o on a.id = o.article_id
having quantity > 0 // or where (i.quantity - o.quantity) > 0

In newer versions of laravel you can use leftJoinSub

$articleIncoming = DB::table('article_incoming_document')
                   ->select('article_id', DB::raw('sum(quantity) as quantity'))
                   ->where('warehouse', $warehouse)
                   ->where('warehouse_type', '=', $warehouseType)
                   ->groupBy('article_id');
                   
$articleOutgoing = DB::table('article_outgoing_document')
                   ->select('article_id', DB::raw('sum(quantity) as quantity'))
                   ->where('warehouse', $warehouse)
                   ->where('warehouse_type', '=', $warehouseType)
                   ->groupBy('article_id');
$articles = DB::table('articles as a')
        ->leftJoinSub($articleIncoming, 'i', function ($join) {
            $join->on('a.id', '=', 'i.article_id');
        })
        ->leftJoinSub($articleOutgoing, 'o', function ($join) {
            $join->on('a.id', '=', 'o.article_id');
        })
        //->where('i/o.quantity', '>', 0)  ?? specify quantity of incoming or outgoing, in case of both then add another where clause
        ->select([
            'a.id',
            'a.name',
            DB::raw('coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity')
        ])
        //->having('quantity', '>', 0) ?? if you want to perform filter on subtraction of incoming and outgoing quantity then use having clause
        ->get();

Additionally I have used coalesce over result of sum(quantity) to ignore nulls like

coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity

DB::raw('coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity')

DEMO

Subtract two columns of different tables

I interpret your remark but that result can't to be negative as requirement to return 0 instead of negative results. The simple solution is GREATEST():

SELECT GREATEST(sum(amount)
      - (SELECT sum(amount)
         FROM   solicitude 
         WHERE  status_id = 1
         AND    user_id = 1), 0) AS total
FROM   contribution
WHERE  user_id = 1;

Otherwise, I kept your original query, which is fine.

For other cases with the possible result that no row could be returned I would replace with two sub-selects. But the use of the aggregate function guarantees a result row, even if the given user_id is not found at all. Compare:

• Get n grouped categories and sum others into one

If the result of the subtraction would be NULL (because no row is found or the sum is NULL), GREATEST() will also return 0.

---

Related Topics