CODEWITHSUNDEEP
mysqlsql
Nathan
Nathan

Reputation: 163

MYSQL:Selecting SUM of a column but the column is based of another row ID

I want to have the sum of the beginning inventory of the entire year. The beginning inventory is based of the end_inventory of another month. The beginning_inventory_id contains the ID of another row which points to the end_inventory. How do I properly get the sum of the beginning_inventory of a certain year when it's based of another row's end_inventory. I have the following table

id time_period beginning_inventory_id end_inventory gross_sales
1 2020-09-01 null 1000 500
2 2020-10-01 1 2000 500
3 2020-11-01 2 3000 500
4 2020-12-01 3 4000 500
5 2021-01-01 4 5000 500

I have the following SQL query

SELECT SUM(a.gross_sales) as gross_sales, SUM(a.end_inventory) as end_inventory,
                    
(SELECT SUM(b.end_inventory) FROM fs_summary as b WHERE a.beginning_inventory_id = b.id) as beginning_inventory
                    
FROM fs_summary as a
                    
WHERE YEAR(a.time_period) = 2020

Output I would like to generate is:

  1. beginning_inventory = 6000
  2. end_inventory = 10000
  3. gross_sales = 2000

Instead, I am getting null on the beginning_inventory.

Any help would be great!

Upvotes: 1

Views: 134

Answers (2)

Bilal Bin Zia
Bilal Bin Zia

Reputation: 694

I am Assuming that you want to retrieve data from 1 table with self join.

SELECT SUM(a.gross_sales),SUM(a.end_inventory),SUM(b.end_inventory)
FROM fs_summary a, fs_aummary b
WHERE b.id=a.beginning_inventory_id AND YEAR(a.time_period) = 2020

using self join can help you in this situation

EDIT: You can also write this script as,

SELECT SUM(a.gross_sales),SUM(a.end_inventory),SUM(b.end_inventory)
FROM fs_summary a
INNER JOIN fs_aummary b
ON b.id=a.beginning_inventory_id 
WHERE YEAR(a.time_period) = 2020

Upvotes: 1

bhavya
bhavya

Reputation: 69

Using self-join SQL you can achieve your result instead of sub-queries.

You should specify the same table with two different names. Your query looks as below

select sum(virtual_tb.end_inventory) as 'beginning_inventory', sum(org_tb.end_inventory) as 'end_inventory', sum(org_tb.gross_sales)  as 'gross_sales'
from fs_summary org_tb left join fs_aummary virtual_tb on (virtual_tb.beginning_inventory_id = org_tb.id) 
where year(org_tb.time_period) = 2020;

(Approx Output)

beginning_inventory end_inventory gross_sales
6000 10000 2000

Upvotes: 1

Related Questions