Get sum from three table and selected data using SQL


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

sql-table-fetch-data-from-three-tables-min.png


Table 2. Raw_item

table-2-min.png

Table 3 . inventory_data

table-3-min.png

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?


Asked by:- RameshwarLate
0
: 5308 At:- 10/15/2018 12:32:57 PM
SQL Sum-of-three-columns data-from-three-columns-sql







1 Answers
profileImage 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_factor

Here is the output, when executed locally

join-multiple-table-with-aggreate-functions-sql-server-min.png

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."

0
At:- 10/17/2018 8:39:54 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use