Answered by:
Sum of three table in sql ?
Question

User367318540 posted
i want this output https://ibb.co/Jz98hwh
here is my data and query in which i have done one table section total ,and need to sum of second table and employee total
CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT)
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,QTY INT,Entrydate DATETIME)
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #Employee(EID INT,SecID INT,QTY int, Entrydate DATETIME)
INSERT INTO #Catagory VALUES(1,'INDIA')
INSERT INTO #Catagory VALUES(2,'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3,'Pakistan')
INSERT INTO #Catagory VALUES(4,'Pakistan(Cut)')
INSERT INTO #Sections VALUES(1,'HR')
INSERT INTO #Sections VALUES(2,'Baby')
INSERT INTO #Sections VALUES(3,'Ladies')
INSERT INTO #Sections VALUES(4,'Mix Rammage')
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,2)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,3)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,null)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,3)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,4)
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01062019')
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02062019')
INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,'03062019')
INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,'04062019')
INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,'04062019')INSERT INTO #Probale VALUES(1,1,1,'01062019')
INSERT INTO #Probale VALUES(2,3,1,'02062019')
INSERT INTO #Probale VALUES(3,11,1,'03062019')
INSERT INTO #Probale VALUES(4,10,1,'08062019')
INSERT INTO #Probale VALUES(3,8,1,'03062019')
INSERT INTO #Probale VALUES(4,9,1,'08062019')
INSERT INTO #Probale VALUES(4,9,1,'08062019')INSERT INTO #Employee VALUES(1,1,4,'01052019')
INSERT INTO #Employee VALUES(2,3,5,'02052019')
INSERT INTO #Employee VALUES(3,3,3,'03052019')
INSERT INTO #Employee VALUES(4,4,7,'04052019')
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01062019'
SET @Enddate = '09062019'
SELECT DISTINCT s.Secnam, ISNULL(SUM(b.prdQTY),0)QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
FULL OUTER JOIN #Sections s ON i.SecID = s.SecIDWHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
GROUP BY s.Secnam
DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
Drop Table #Employee
Drop Table #ProbaleThursday, June 27, 2019 5:44 AM
Answers

User719153870 posted
Hi akhterr,
According to the code you provide, it's not difficult to get the fourth column 'Total'.
To get the last row 'Total' as the statistics of the data in the table, you can add it by ‘union all’ of SQL and requerying the result set.
But with regard to the last column of data, I can't guess how it was generated by the information you've provided so it's difficult to provide the data for me.
You can describe the relationship between your tables and how the last column was generated, so I may help you more.
For current progress, please refer to below codes in SQL:
DECLARE @StartDate DATETIME, @Enddate DATETIME SET @StartDate = '01062019' SET @Enddate = '09062019' SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam union all select 'Total' as Secnam,SUM(k.[Bigbale Sum QTY]) as [Bigbale Sum QTY],SUM(k.[Probale Sum QTY]) as [Probale Sum QTY],SUM(k.[Total]) as [Total] from (SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam )k
Here is result of my current work:
Best Regard,
Yang Shen
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 27, 2019 9:03 AM
All replies

User719153870 posted
Hi akhterr,
According to the code you provide, it's not difficult to get the fourth column 'Total'.
To get the last row 'Total' as the statistics of the data in the table, you can add it by ‘union all’ of SQL and requerying the result set.
But with regard to the last column of data, I can't guess how it was generated by the information you've provided so it's difficult to provide the data for me.
You can describe the relationship between your tables and how the last column was generated, so I may help you more.
For current progress, please refer to below codes in SQL:
DECLARE @StartDate DATETIME, @Enddate DATETIME SET @StartDate = '01062019' SET @Enddate = '09062019' SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam union all select 'Total' as Secnam,SUM(k.[Bigbale Sum QTY]) as [Bigbale Sum QTY],SUM(k.[Probale Sum QTY]) as [Probale Sum QTY],SUM(k.[Total]) as [Total] from (SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam )k
Here is result of my current work:
Best Regard,
Yang Shen
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 27, 2019 9:03 AM 
User367318540 posted
Dear Yang Shen,
this error is coming when i am give date parameter like this
SET @StartDate = '01062019'
SET @Enddate = '17062019'and how to join employee table
Msg 241, Level 16, State 1, Line 64
Conversion failed when converting date and/or time from character string.Thursday, June 27, 2019 12:19 PM 
User719153870 posted
Hi akhterr,
The format of @StartDate and @Enddate is as "MMDDYYYY", so obviously 17 is not a month.
As to the Employee, please refer to below codes:
DECLARE @StartDate DATETIME, @Enddate DATETIME SET @StartDate = '01062019' SET @Enddate = '09062019' SELECT A.Secnam,A.[Bigbale Sum QTY],A.[Probale Sum QTY],A.Total, B.ETotal FROM ( select row_number() over(order by mnm.Secnam asc) as rn,mnm.* from ( SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam ) mnm ) A FULL JOIN ( select row_number() over(order by s.Secnam asc) as rn,s.Secnam as SecName,ISNULL(SUM(ee.QTY),0) as [ETotal] from #Sections s left join (select * from #Employee e where e.Entrydate between '01062019' and '09062019') ee on ee.SecID=s.SecID group by s.Secnam ) B ON A.Secnam=B.SecName union all select 'Total' as Secnam,SUM(k.[Bigbale Sum QTY]) as [Bigbale Sum QTY],SUM(k.[Probale Sum QTY]) as [Probale Sum QTY],SUM(k.[Total]) as [Total],SUM(k.ETotal) as [ETotal] from (SELECT A.Secnam,A.[Bigbale Sum QTY],A.[Probale Sum QTY],A.Total, B.ETotal FROM ( select row_number() over(order by mnm.Secnam asc) as rn,mnm.* from ( SELECT DISTINCT s.Secnam,ISNULL(SUM(g.QTY),0) as [Bigbale Sum QTY], ISNULL(SUM(b.prdQTY),0) as [Probale Sum QTY],ISNULL(SUM(g.QTY),0)+ISNULL(SUM(b.prdQTY),0) as [Total] FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd g ON i.CodeItem = g.CodeItem LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem FULL OUTER JOIN #Sections s ON i.SecID = s.SecID WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL and g.Entrydate BETWEEN @StartDate AND @Enddate OR g.Entrydate IS NULL GROUP BY s.Secnam ) mnm ) A FULL JOIN ( select row_number() over(order by s.Secnam asc) as rn,s.Secnam as SecName,ISNULL(SUM(ee.QTY),0) as [ETotal] from #Sections s left join (select * from #Employee e where e.Entrydate between '01062019' and '09062019') ee on ee.SecID=s.SecID group by s.Secnam ) B ON A.Secnam=B.SecName )k
Here's result of my demo:
Best Regard,
Yang Shen
Friday, June 28, 2019 7:38 AM