Hello, I am trying to get Sum of three columns from three different table and get results table using SQL.
Suppose here are my tables
Table 1: raw_item_fcst
Table 2. Raw_item
Table 3 . inventory_data
I try to write query to fetch data from these tables using subquery , Here is my sub query
Select distinct(raw_item) (SELECT sum(FINAL_ADJ_FCST_QTY) + (select sum(safety_stock)
-
(SELECT sum(Ending_Inventory))
FROM inventory_data
WHERE business_day
BETWEEN CONVERT(datetime, '2018-10-15') AND CONVERT(datetime, '2018-10-17')
AND
rest_id = 101 AND raw_id = 'R1')
FROM raw_item_fcst
WHERE business_day BETWEEN CONVERT(datetime, '2018-10-15') AND
CONVERT(datetime, '2018-10-17') AND rest_id = 101 AND raw_id = 'R1')
As Total from raw_item where category=’Vegetables’;
And I want output as List Of raw_item with Category Vegetables
| Category | Raw_item | Resulted Values |
| Vegetables | Onion | Value |
And sum of three colummns
(SELECT sum(FINAL_ADJ_FCST_QTY) + (select sum(safety_stock)
- (SELECT sum(Ending_Inventory))
How can I do that?
Answered by:- vikas_jk
You need to use Join and Group By Clause
SELECT C.rest_id, C.category,C.raw_id,C.category,C.inv_cor_factor,C.hindi_name,
Sum(CA.FINAL_ADJ_FCST_QTY) + Sum(L.safety_stock) - Sum (L.Ending_Inventory) as SUMTotal
FROM raw_item C
LEFT JOIN inventory_data L ON C.raw_id=L.raw_id
LEFT JOIN raw_item_fcst CA ON L.raw_id=CA.raw_id
Where
C.category='Vegetables' and C.rest_id=101 and CA.business_day
BETWEEN
CONVERT(datetime, '2018-10-15') AND CONVERT(datetime, '2018-10-17')
AND
L.rest_id = 101 AND CA.raw_id = 'R1' or CA.raw_id='R106' or CA.raw_id='R3'
GROUP BY C.raw_id,C.rest_id,C.category,C.raw_item,C.hindi_name,c.inv_cor_factorHere is the output, when executed locally
You can add more columns in Select query, but you would have to add same column name in GroupBy, to avoid syntax errors like "Column 'TableName.ColumnName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly
Buy us a coffee
Become a Patron