 # Calculate percentage

Hi.
I wasn't expecting to be that complicated or I'm missing something.
I want to get the percentage of reserved vs unreserved.
So the tables has:
Barcode , Vtype, Isreserved
1111111,1,0
1111113,1,1
1111112,1,2
2111115,2,1
2111116,2,0
2111118,2,1
etc

The full select would be :

``````SELECT  [BarCode]
,[VType]
,[IsReserved]
FROM [tblOPVoucher]``````

What I'm trying as a result is this:

Vtype,PercentageReserverd,Percentagenoreserved
1,88,22
2,16,84

I can't even do the first calculation , it will bring out zero.

`````` select [VType], ( (select count(*) from [[tblOPVoucher] where VType=1)-(select count(*)
from [tblOPVoucher] where VType=1 and IsReserved =1) ) / (select count(*) from [tblOPVoucher] where VType=1) * 100 as T1
FROM [tblOPVoucher]

group by VType``````

this will give
VType T1
1 0
2 0

``````select [VType], ( (select count(*) from [tblOPVoucher] where VType=1)-(select count(*)
from [tblOPVoucher] where VType=1 and IsReserved =1) )
FROM [tblOPVoucher]

group by VType``````

This will give:
VType (No column name)
1 999
2 999

So I was expecting a 99 .

So is there a problem here with the calculations? Must I declare a double somewhere? Is there a better approach?

Thanks.

I'm adding a twist here. If it's easier, I would like to get the total number and not the percentage.
So the issue here is that I get the total calculation of all the type, I would like it split into per type
So:
Type,Reserved,Remain
1,400,100
2,500,33

``````   select [VType],  (select count(*) from [tblOPVoucher] where [IsReserved]=1)
,(select count(*) from [tblOPVoucher] where  IsReserved =0)
FROM [tblOPVoucher]
group by VType``````

this will give the full amount
1,900,133
2,900,133

hope this helps

``````select
[VType] ,
sum(case when [IsReserved] = 1 then 1 else 0 end ) as Reserved ,
sum(case when [IsReserved] = 0 then 1 else 0 end ) as Remain
from
[tblOPVoucher]
group by
VType``````
1 Like

Yes this helps a lot. That was so simple, I was experimenting with partitions and such but this is, like, DUH!

For clarity - the problem with your calculation is due to integer math.

``````Select 99 / 100 * 100
, 99.0 / 100 * 100;
``````

The first will return 0 - the second returns 99.000000.

1 Like

Im out of the office right now but , if I use a ceiling function will it suffice?

No - integer division will not return anything but an integer. You have to convert one or both values in the calculation to a numeric value. The ceiling of (99 / 100 * 100) would still be zero - and if you are thinking of using ceiling after converting, then you might not get the results expected. For example, the ceiling of 45.0 / 99 * 100 would be 46 and the actual result should be 45.454500.

1 Like

Got it.
Thanks