Calculate percentage

1

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.

2

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

3

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
4

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

5

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
6

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

7

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
8

Got it.
Thanks