CODEWITHSUNDEEP
phpmysqllaravel
Code Embassy
Code Embassy

Reputation: 247

mysql subtract one select query from another select query value

I have two mysql table and I have build to select query using this two tables, both query output is a summation of quantity from inventory_transfer_details table. I need to subtract "total_to" from "total_from". please check below is mysql tables.

Inventory_transfer enter image description here

inventory_transfer_details enter image description here

below is my two queries.

First query:

select sum(b.transfer_quantity) as total_to 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.to_warehouse_id = '2'

second query:

select sum(b.transfer_quantity) as total_from 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.from_warehouse_id = '2'

I need to subtract from query transfer_to to transfer_from

can you have any guide to convert this final query to laravel query?

Upvotes: 1

Views: 2505

Answers (2)

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You can combine those queries and get the difference

SELECT
  sum(
    IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)
  ) - sum(
    IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)
  ) as total
FROM
  inventory_transfers AS a
  JOIN inventory_transfer_details AS b ON a.id = b.inventory_transfer_id
WHERE
  a.status = "approved"
  AND b.inventory_or_composite_id = '1'

In the laravel query builder, this query might look like

DB::table('inventory_transfers as a')
    ->select(DB::raw('sum(IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)) - sum(IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)) as total'))
    ->join('inventory_transfer_details as b', DB::raw('a.id'), '=', DB::raw('b.inventory_transfer_id'))
    ->where([
        ['a.status', '=', 'approved']
        ['b.inventory_or_composite_id', '=', 1]
    ])
    ->get()

Upvotes: 4

HoldOffHunger
HoldOffHunger

Reputation: 20861

I wanted to provide a more generalized answer to this problem: subtracting any SUM() from any other SUM(). You could simply do...

SELECT Total1, Total2, Total1 - Total2
FROM
    (SELECT SUM(id) Total1 FROM TableA) AS a
INNER JOIN
    (SELECT SUM(id) Total2 FROM TableB) AS b;

In your case, the two subqueries, a and b, would be replaced with the two queries you currently have for calculating sums.

Upvotes: 1

Related Questions