locked
Calculating Percentage row wise but it just given % of one row RRS feed

  • Question

  • User-367318540 posted
    CREATE TABLE #Category (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,weight int);
    CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int);
    CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
    GO
    INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')
    INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
    
    INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
    , (2,'B',2,2,100)
    , (3,'C',3,3,100)
    , (4,'D',4,null,100)
    , (5,'e',5,null,100)
    , (6,'f',6,null,100)
    , (7,'g',4,2,100)
    , (8,'h',4,3,100)
    , (9,'K',2,2,100)
    , (10,'L',4,3,100)
    , (11,'M',2,4,100);
    INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
    , (2,3,3,3,1,'02-06-2019',null,100)
    , (3,4,null,4,1,'03-06-2019',null,100)
    , (4,4,null,4,1,'04-06-2019',null,100)
    , (4,5,null,4,1,'04-06-2019',null,100);
    
    INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
    , (2,3,1,200,'02-06-2019',null)
    , (3,11,1,200,'03-06-2019',null)
    , (4,10,1,200,'08-06-2019',null)
    , (3,8,1,200,'03-06-2019',null)
    , (4,9,1,200,'08-06-2019',null)
    , (4,9,1,200,'08-06-2019',null);
    
    INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
    , (2,3,5,'02-05-2019')
    , (3,3,3,'03-05-2019')
    , (4,4,7,'04-05-2019');
    
    DECLARE @StartDate DATETIME, @Enddate DATETIME
    SET @StartDate = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    ;WITH emp
    
    as
    (
    select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s
    left join #Emp_Strength e on s.secid=e.secid
    where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)
    group by Secnam
    ),cte
    AS
    (
    SELECT DISTINCT Sec.Secnam, 
    ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,
    ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight
    --ISNULL(SUM(emp.QTY),0)Employee_QTY
    FROM #ItemMasterFile i
    LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
    LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem
    Full Outer Join #Sections sec on i.SecID=sec.SecID
    --left join Emp_Strength emp on emp.SecID = sec.SecID
    --FULL OUTER JOIN Sections s ON i.SecID = s.SecID
    where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null ) 
    and
    (b1.DelID is null and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )
    
    GROUP BY sec.Secnam
    ),cte1 as ( 
    SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight, 
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_QTY)
    , (Smallbale_QTY)
    
    ) AS value(val)
    ) AS Total_QTY,
    
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    ) as Total_Weight,
    
    --For Percentage--
    
    ( Select (ISNULL(Small_Bale_weight,0))/((SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    ))*100.0) as Percentage,
    
    
    coalesce(Employee_QTY,0) Employee_QTY
    FROM cte left join emp on cte.secnam=emp.secnam
    group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
    
    
    
    select * from cte1
    where  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0
    

    here is my output   https://ibb.co/Kmw1TmN

    Image

    Tuesday, July 2, 2019 2:38 PM

Answers

  • User-719153870 posted

    Hi akhterr,

    The reason why you encounter this problem is that in SQL, when a smaller integer is divided by a larger integer, you will get 0.

    You need to deal with the value in the expression.

    It is suggested that you can first convert the divisor to decimal, and then reserve a decimal for the final result value.

    Please refer to below SQL codes:

    CREATE TABLE #Category (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,weight int);
    CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int);
    CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
    GO
    INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')
    INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
    
    INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
    , (2,'B',2,2,100)
    , (3,'C',3,3,100)
    , (4,'D',4,null,100)
    , (5,'e',5,null,100)
    , (6,'f',6,null,100)
    , (7,'g',4,2,100)
    , (8,'h',4,3,100)
    , (9,'K',2,2,100)
    , (10,'L',4,3,100)
    , (11,'M',2,4,100);
    INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
    , (2,3,3,3,1,'02-06-2019',null,100)
    , (3,4,null,4,1,'03-06-2019',null,100)
    , (4,4,null,4,1,'04-06-2019',null,100)
    , (4,5,null,4,1,'04-06-2019',null,100);
    
    INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
    , (2,3,1,200,'02-06-2019',null)
    , (3,11,1,200,'03-06-2019',null)
    , (4,10,1,200,'08-06-2019',null)
    , (3,8,1,200,'03-06-2019',null)
    , (4,9,1,200,'08-06-2019',null)
    , (4,9,1,200,'08-06-2019',null);
    
    INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
    , (2,3,5,'02-05-2019')
    , (3,3,3,'03-05-2019')
    , (4,4,7,'04-05-2019');
    
    DECLARE @StartDate DATETIME, @Enddate DATETIME
    SET @StartDate = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    ;WITH emp
    
    as
    (
    select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s
    left join #Emp_Strength e on s.secid=e.secid
    where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)
    group by Secnam
    ),cte
    AS
    (
    SELECT DISTINCT Sec.Secnam, 
    ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,
    ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight
    --ISNULL(SUM(emp.QTY),0)Employee_QTY
    FROM #ItemMasterFile i
    LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
    LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem
    Full Outer Join #Sections sec on i.SecID=sec.SecID
    --left join Emp_Strength emp on emp.SecID = sec.SecID
    --FULL OUTER JOIN Sections s ON i.SecID = s.SecID
    where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null ) 
    and
    (b1.DelID is null and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )
    
    GROUP BY sec.Secnam
    ),cte1 as ( 
    SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight, 
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_QTY)
    , (Smallbale_QTY)
    
    ) AS value(val)
    ) AS Total_QTY,
    
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    ) as Total_Weight,
    
    --For Percentage--
    
    ( Select convert(decimal(18,1),(cast(ISNULL(Small_Bale_weight,0)as decimal))/((SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    )))*100) as Percentage,
    
    coalesce(Employee_QTY,0) Employee_QTY
    FROM cte left join emp on cte.secnam=emp.secnam
    group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
    
    select * from cte1
    where  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0

    Here is result of my demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 3, 2019 5:08 AM

All replies

  • User-719153870 posted

    Hi akhterr,

    The reason why you encounter this problem is that in SQL, when a smaller integer is divided by a larger integer, you will get 0.

    You need to deal with the value in the expression.

    It is suggested that you can first convert the divisor to decimal, and then reserve a decimal for the final result value.

    Please refer to below SQL codes:

    CREATE TABLE #Category (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,weight int);
    CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int);
    CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int);
    CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
    GO
    INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')
    INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');;
    
    INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)
    , (2,'B',2,2,100)
    , (3,'C',3,3,100)
    , (4,'D',4,null,100)
    , (5,'e',5,null,100)
    , (6,'f',6,null,100)
    , (7,'g',4,2,100)
    , (8,'h',4,3,100)
    , (9,'K',2,2,100)
    , (10,'L',4,3,100)
    , (11,'M',2,4,100);
    INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019',null,100)
    , (2,3,3,3,1,'02-06-2019',null,100)
    , (3,4,null,4,1,'03-06-2019',null,100)
    , (4,4,null,4,1,'04-06-2019',null,100)
    , (4,5,null,4,1,'04-06-2019',null,100);
    
    INSERT INTO #Probale VALUES(1,1,1,100,'01-06-2019',null)
    , (2,3,1,200,'02-06-2019',null)
    , (3,11,1,200,'03-06-2019',null)
    , (4,10,1,200,'08-06-2019',null)
    , (3,8,1,200,'03-06-2019',null)
    , (4,9,1,200,'08-06-2019',null)
    , (4,9,1,200,'08-06-2019',null);
    
    INSERT INTO #Emp_Strength VALUES(1,1,4,'01-05-2019')
    , (2,3,5,'02-05-2019')
    , (3,3,3,'03-05-2019')
    , (4,4,7,'04-05-2019');
    
    DECLARE @StartDate DATETIME, @Enddate DATETIME
    SET @StartDate = '01-06-2019'
    SET @Enddate = '09-06-2019'
    
    ;WITH emp
    
    as
    (
    select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s
    left join #Emp_Strength e on s.secid=e.secid
    where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)
    group by Secnam
    ),cte
    AS
    (
    SELECT DISTINCT Sec.Secnam, 
    ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,
    ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight
    --ISNULL(SUM(emp.QTY),0)Employee_QTY
    FROM #ItemMasterFile i
    LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem
    LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem
    Full Outer Join #Sections sec on i.SecID=sec.SecID
    --left join Emp_Strength emp on emp.SecID = sec.SecID
    --FULL OUTER JOIN Sections s ON i.SecID = s.SecID
    where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null ) 
    and
    (b1.DelID is null and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )
    
    GROUP BY sec.Secnam
    ),cte1 as ( 
    SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight, 
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_QTY)
    , (Smallbale_QTY)
    
    ) AS value(val)
    ) AS Total_QTY,
    
    ( SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    ) as Total_Weight,
    
    --For Percentage--
    
    ( Select convert(decimal(18,1),(cast(ISNULL(Small_Bale_weight,0)as decimal))/((SELECT SUM(val) 
    FROM (VALUES (Bigbale_Weight),
    (Small_Bale_weight )
    )AS value(val)
    )))*100) as Percentage,
    
    coalesce(Employee_QTY,0) Employee_QTY
    FROM cte left join emp on cte.secnam=emp.secnam
    group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
    
    select * from cte1
    where  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0

    Here is result of my demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 3, 2019 5:08 AM
  • User-367318540 posted

    Hi Yang Shen

    thanks for reply ,,

    i want to calculate Small_Bale_Weight/Total_weight*100   For example (2400/12800*100)..

    Wednesday, July 3, 2019 5:40 AM
  • User-719153870 posted

    Hi akhterr,

    The result set I provided previously is Percentage=Small_Bale_Weight/Total_weight*100 already.

    For example, in the second line 50.0=1600/3200*100.

    Best Regard,

    Yang Shen

    Wednesday, July 3, 2019 8:48 AM
  • User-367318540 posted

    hi Yang Shen

    sorry ,i could not make you clear ,that Grand Total of Total_Weight  column will be divided.    

    Wednesday, July 3, 2019 8:59 AM
  • User-719153870 posted

    Hi akhterr,

    In a single query, it is not possible to get the total value of a column while trying to query each row.

    For example, when we query the first row, we can't get the sum of Total_weight because the query for the other rows has not yet started.

    So if you still want to get your goal, there are two options:

    First, give up using 'with_select_', because in this case, your data coupling is too high;

    Second, you can describe in detail how the three data: Small_Bale_weight, Bigbale_Weight and Total_Weight are queried from your table.

    Knowing these information may help us meet your needs.

    Best Regard,

    Yang Shen

    Friday, July 5, 2019 9:55 AM