We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Calculating Percentage row wise but it just given % of one row[Answered] RSS

## 5 replies

Last post Jul 05, 2019 09:55 AM by Yang Shen

• akhterr

Member

118 Points

282 Posts

### Calculating Percentage row wise but it just given % of one row

```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 #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

• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Calculating Percentage row wise but it just given % of one row

Jul 03, 2019 05:08 AM|Yang Shen|LINK

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 #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

• akhterr

Member

118 Points

282 Posts

### Re: Calculating Percentage row wise but it just given % of one row

Hi Yang Shen

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

• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Calculating Percentage row wise but it just given % of one row

Jul 03, 2019 08:48 AM|Yang Shen|LINK

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

• akhterr

Member

118 Points

282 Posts

### Re: Calculating Percentage row wise but it just given % of one row

hi Yang Shen

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

• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Calculating Percentage row wise but it just given % of one row

Jul 05, 2019 09:55 AM|Yang Shen|LINK

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