locked
Sum of three table in sql ? RRS feed

  • Question

  • User-367318540 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,'01-06-2019')
    INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019')
    INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,'03-06-2019')
    INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,'04-06-2019')
    INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,'04-06-2019')

    INSERT INTO #Probale VALUES(1,1,1,'01-06-2019')
    INSERT INTO #Probale VALUES(2,3,1,'02-06-2019')
    INSERT INTO #Probale VALUES(3,11,1,'03-06-2019')
    INSERT INTO #Probale VALUES(4,10,1,'08-06-2019')
    INSERT INTO #Probale VALUES(3,8,1,'03-06-2019')
    INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')
    INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')

    INSERT INTO #Employee VALUES(1,1,4,'01-05-2019')
    INSERT INTO #Employee VALUES(2,3,5,'02-05-2019')
    INSERT INTO #Employee VALUES(3,3,3,'03-05-2019')
    INSERT INTO #Employee VALUES(4,4,7,'04-05-2019')

    DECLARE @StartDate DATETIME, @Enddate DATETIME
    SET @StartDate = '01-06-2019'
    SET @Enddate = '09-06-2019'

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

    WHERE (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 #Probale

    Thursday, June 27, 2019 5:44 AM

Answers

  • User-719153870 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 re-querying 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 = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    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

  • User-719153870 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 re-querying 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 = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    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
  • User-367318540 posted

    Dear Yang Shen,

    this error is coming when i am give date parameter like this

    SET @StartDate = '01-06-2019'
    SET @Enddate = '17-06-2019'

    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
  • User-719153870 posted

    Hi akhterr,

    The format of @StartDate and @Enddate is as "MM-DD-YYYY", so obviously 17 is not a month.

    As to the Employee, please refer to below codes:

    DECLARE @StartDate DATETIME, @Enddate DATETIME
    SET @StartDate = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    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 '01-06-2019' and '09-06-2019') 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 '01-06-2019' and '09-06-2019') 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