Results 1 to 14 of 14

Thread: calculate percentage

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: calculate percentage

    Quote Originally Posted by sapator View Post
    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>

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: calculate percentage

    Quote Originally Posted by sapator View Post
    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>

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  10. #10

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  12. #12

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: calculate percentage

    Thanks.
    I think in mssql 2012 is NULLIF not IFNULL
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: calculate percentage

    Quote Originally Posted by sapator View Post
    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

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: calculate percentage

    Quote Originally Posted by Zvoni View Post
    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
  •  



Click Here to Expand Forum to Full Width