-
Nov 18th, 2021, 04:27 AM
#1
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
Code:
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.
Code:
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
Code:
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,9 .
So is there a problem here with the calculations? Must I declare a double somewhere? Is there a better approach?
Thanks.
Last edited by sapator; Nov 18th, 2021 at 05:03 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 04:58 AM
#2
Re: calculate percentage
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
Code:
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
Last edited by sapator; Nov 18th, 2021 at 05:04 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 05:52 AM
#3
Re: calculate percentage
Originally Posted by sapator
I wasn't expecting to be that complicated or I'm missing something.
If you have this
a = 1 / 2
. . . and this
b = 1.0 / 2
. . . then what is the value of a and b? Is it possible a is zero but b is not?
Then think about the data-type of this
c = (SELECT COUNT(*) FROM ...) / 2
. . . vs this
d = (SELECT 0.0 + COUNT(*) FROM ...) / 2
cheers,
</wqw>
-
Nov 18th, 2021, 05:52 AM
#4
Re: calculate percentage
1) Is this a Typo?
"1111112,1,2" --> IsReserved has Value 2
in your WHERE-Clause you compare IsReserved=1 --> meaning that one with Value 2 is ignored
2) How do you figure in your first example, that VType 1 is 88% reserved?!??! I would expect 66% (2 of 3)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 18th, 2021, 06:12 AM
#5
Re: calculate percentage
Yes it's a typo.
For the simple substruct I can do this:
Code:
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
For the percentage, I could get by without the "," comma so a plain Int can be fine.
Also I'm thinking when something reaches zero, I will have a crash so something must be done.
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 06:19 AM
#6
Re: calculate percentage
Originally Posted by sapator
Also I'm thinking when something reaches zero, I will have a crash so something must be done.
To prevent division by zero in a / b you can use something like a / NULLIF(b, 0) so you get NULLs in the UI.
You can even set a default value in the data-type of the calculation with something like COALESCE(a / NULLIF(b, 0), -1) to get -1 for "impossible" values.
Edit: Oh, now I get it. . . I must be in OP's ignore list! Luckily he is not moderator so I can reciprocate at will :-))
cheers,
</wqw>
-
Nov 18th, 2021, 06:23 AM
#7
Re: calculate percentage
Thanks,
Now I was thinking something like this might work:
Code:
select
[VType] ,
CEILING ((sum(case when [IsReserved] = 1 then 1 else 0 end ) ) / (sum(case when [IsReserved] = 0 then 1 else 0 end ) ) * 100) ,
sum(case when [IsReserved] = 0 then 1 else 0 end ) as Remain
from
[tblOPVoucher]
group by
VType
This is what I think: The first sum brings 1divide by the second sum this brings 1/ 999 * 100 , I was expecting a 0,1 and using the Ceiling to elevate it.
Am I doing something wrong? Do i need to swift the parenthesis elsewhere?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 06:32 AM
#8
Re: calculate percentage
Your Percentage-Thing:
Tested in SQLite
Code:
SELECT
T1.VType,
IFNULL(T2.Reserved / (SELECT COUNT(Barcode) FROM tblOPVoucher)*100,0) As ReservedPerc,
IFNULL(T3.NoReserved / (SELECT COUNT(Barcode) FROM tblOPVoucher)*100,0) As NoReservedPerc,
(SELECT COUNT(Barcode) FROM tblOPVoucher) As Total
FROM tblOPVoucher T1
LEFT JOIN
(SELECT VType, Count()*1.0 As Reserved FROM tblOPVoucher WHERE IsReserved<>0 GROUP BY VType) AS T2
ON
T2.VType=T1.VType
LEFT JOIN
(SELECT VType, Count()*1.0 As NoReserved FROM tblOPVoucher WHERE IsReserved=0 GROUP BY VType) AS T3
ON
T3.VType=T1.VType
Last edited by Zvoni; Nov 18th, 2021 at 06:37 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 18th, 2021, 06:34 AM
#9
Re: calculate percentage
The only crash you can get, is when you don't have any Barcodes => The Table is Empty
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 18th, 2021, 06:37 AM
#10
Re: calculate percentage
Thanks.
That is super complicated for me, I would have tried something with partition and over not that one as I won't be able to replicate it in another scenario but thanks.
Also some adjustment was needed for grouping and count:
Code:
SELECT
T1.VType,
T2.Reserved / (SELECT COUNT(Barcode) FROM tblOPVoucher)*100 As ReservedPerc,
T3.NoReserved / (SELECT COUNT(Barcode) FROM tblOPVoucher)*100 As NoReservedPerc,
(SELECT COUNT(Barcode) FROM tblOPVoucher) As Total
FROM tblOPVoucher T1
INNER JOIN
(SELECT VType, Count(*)*1.0 As Reserved FROM tblOPVoucher WHERE IsReserved<>0 GROUP BY VType) AS T2
ON
T2.VType=T1.VType
INNER JOIN
(SELECT VType, Count(*)*1.0 As NoReserved FROM tblOPVoucher WHERE IsReserved=0 GROUP BY VType) AS T3
ON
T3.VType=T1.VType
group by
T1.VType,T2.VType,t3.VType,T2.Reserved,t3.NoReserved
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 06:38 AM
#11
Re: calculate percentage
I edited my post: Use LEFT JOIN instead of INNER JOIN and look at the IFNULL's
This gives the Percentage of each VType to the Total of all Barcodes
If you want the Percentage Within a VType-Group we have to change it.
Give me second
EDIT: This will give Percentage Within a VType-Group
Code:
SELECT
T1.VType,
IFNULL(T2.Reserved / (SELECT COUNT(Barcode) FROM tblOPVoucher WHERE VType=T1.VType)*100,0) As ReservedPerc,
IFNULL(T3.NoReserved / (SELECT COUNT(Barcode) FROM tblOPVoucher WHERE VType=T1.VType)*100,0) As NoReservedPerc,
(SELECT COUNT(Barcode) FROM tblOPVoucher) As Total
FROM tblOPVoucher T1
LEFT JOIN
(SELECT VType, Count()*1.0 As Reserved FROM tblOPVoucher WHERE IsReserved<>0 GROUP BY VType) AS T2
ON
T2.VType=T1.VType
LEFT JOIN
(SELECT VType, Count()*1.0 As NoReserved FROM tblOPVoucher WHERE IsReserved=0 GROUP BY VType) AS T3
ON
T3.VType=T1.VType
Last edited by Zvoni; Nov 18th, 2021 at 06:42 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 18th, 2021, 07:21 AM
#12
Re: calculate percentage
Thanks.
I think in mssql 2012 is NULLIF not IFNULL
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Nov 18th, 2021, 07:35 AM
#13
Re: calculate percentage
Originally Posted by sapator
Thanks.
I think in mssql 2012 is NULLIF not IFNULL
I Wrote "Tested in SQLite"
Each DBMS has its own Dialect/Syntax
I could not detect which DBMS you are using
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 21st, 2021, 09:12 AM
#14
Re: calculate percentage
Originally Posted by Zvoni
I Wrote "Tested in SQLite"
Each DBMS has its own Dialect/Syntax
Yep, the nice thing in SQLite is, that it treats Boolean (Bit) Fields like a normal Integer -
so one can use it easily in Math-Ops directly (without casting) - to avoid Extra-Where-clauses.
E.g. stuff like: Sum (IsAvailable * Price)
Adapted to sapators case, we could write (in SQLite):
Code:
Select VType, Sum(IsReserved) R1, Sum(1-IsReserved) R0, Count(*) CC
From T Group By VType
To get a result like:
Code:
VType R1 R0 CC
1 2 1 3
2 3 1 4
From (full VB6-code below:
Code:
Private Sub Form_Load()
With New_c.Connection(, DBCreateInMemory)
.Execute "Create Table T(VType Int, IsReserved Bool)"
.Execute "Insert Into T Values(1, 0)"
.Execute "Insert Into T Values(1, 1)"
.Execute "Insert Into T Values(1, 1)"
.Execute "Insert Into T Values(2, 1)"
.Execute "Insert Into T Values(2, 0)"
.Execute "Insert Into T Values(2, 1)"
.Execute "Insert Into T Values(2, 1)"
Dim Rs As cRecordset
Set Rs = .GetRs("Select VType, Sum(IsReserved) R1, Sum(1-IsReserved) R0, Count(*) CC From T Group By VType")
Do Until Rs.EOF
If Rs.AbsolutePosition = 1 Then Debug.Print Rs(0).Name, Rs(1).Name, Rs(2).Name, Rs(3).Name
Debug.Print Rs(0).Value, Rs(1).Value, Rs(2).Value, Rs(3).Value
Rs.MoveNext
Loop
End With
End Sub
With TSQL, one can do the same (using Cast(TheBitField As Int)) -
which is a bit more "noisy", but still better readable than Case When constructs.
Code:
Select VType
,Sum( Cast(IsReserved As Int)) R1
,Sum(1-Cast(IsReserved As Int)) R0
,Count(*) CC
From T Group By VType
Here is the above adapted to TSQL in a Fiddle:
http://sqlfiddle.com/#!18/d4c34/2
HTH
Olaf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|