Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculate multiple columns

    Hi,

    I have a table (tbl_res) in which I have 18 columns named AMT1 - AMT18 and 1 column PlayerId (text).
    I want to count how many times those 18 columns (AMT1 - AMT18) got the value 1 for a PlayerId so that I can use that number to calculate a % later on.

    What is the best way of doing this, I guess it's best to count it in SQL?

    All help very much appreciated!
    (Links, Code, Pointing in the right direction)

    /S

    PS! I am totally new to this (please go easy on me), I try to learn as the guy who coded this has left us (RIP) DS!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Ugh. Very bad database design. You are going to pay the price for that guy's mistakes.

    Let's make sure I understand you:

    Suppose you have playerID of 347 who has these values in the 18 columns:

    347 0 0 0 1 0 0 1 0 0 0 1 0 0 0 0 1 0 1

    What you want to end up with is

    347 5 (there were 5 1's in the 18 fields)

    Yes? No?

    Oh...and what database is this? MySQL? SQL Server? Access? Other?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi,

    first off it's Access database.
    Second thing is that he made the PlayerId text, lets say we have John Doe
    his PlayerId is JohDoe.

    All 18 columns has a value between 1 and 7.

    A bit of background, this is for a miniature golf clubs statistics page.
    They asked me to try to find a way to calculate how many % a player
    ended up with 1 stroke, 2 strokes, 3 strokes and so on up to 7 strokes that is
    the maximum strokes you can have on each hole in a competition.

    So to make an example lets say a player has played 10 competitions of 2 rounds each. That would make 10 * 2 = 20 rounds each round is 18 holes
    so the player have played 360 holes. Let's say he scored a hole in one 73 times.

    What I want to end up with then is 73 (strokes) / 360 (lanes) * 100 = 20,28%

    Hope that explains it in an understandable way.

    /S

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    As I said, you predecessor really really messed you over with that terrible DB design.

    I assume you don't want to take the time to go make it right? Presumably there's a bunch of other code that now depends on the current bad design?

    SO...

    Best you can do:
    Code:
    SELECT playerid, SUM(
                IIF(amt1=1,1,0) + IIF(amt2=1,1,0) + IIF(amt3=1,1,0) + IIF(amt4=1,1,0) +
                IIF(amt5=1,1,0) + IIF(amt6=1,1,0) + IIF(amt7=1,1,0) + IIF(amt8=1,1,0) +
                IIF(amt9=1,1,0) + IIF(amt10=1,1,0) + IIF(amt11=1,1,0) + IIF(amt12=1,1,0) +
                IIF(amt13=1,1,0) + IIF(amt14=1,1,0) + IIF(amt15=1,1,0) + IIF(amt16=1,1,0) +
                IIF(amt17=1,1,0) + IIF(amt18=1,1,0) 
              ) AS holesInOne
    FROM yourtable
    GROUP BY playerid
    ORDER BY playerid
    For "holesInTwo" you would need to change each =1 to =2, etc.

    So if you are doing this in ASP code, you might want to do this:
    Code:
    <%
    SQL = "SELECT playerid, SUM(" _
        &      "IIF(amt1=$,1,0) + IIF(amt2=$,1,0) + IIF(amt3=$,1,0) + IIF(amt4=$,1,0) + " _
        &      "IIF(amt5=$,1,0) + IIF(amt6=$,1,0) + IIF(amt7=$,1,0) + IIF(amt8=$,1,0) + " _
        &      "IIF(amt9=$,1,0) + IIF(amt10=$,1,0) + IIF(amt11=$,1,0) + IIF(amt12=$,1,0) + " _
        &      "IIF(amt13=$,1,0) + IIF(amt14=$,1,0) + IIF(amt15=$,1,0) + IIF(amt16=$,1,0) + " _
        &      "IIF(amt17=$,1,0) + IIF(amt18=$,1,0) " _
        &     ") AS holesIn$" _
        & " FROM yourtable " _
        & " GROUP BY playerid " _
        & " ORDER BY playerid "
    holesIn = 2 ' or 1 or 3 or whatever
    
    SQL = Replace( SQL, "$", holesIn )
    
    Set RS = conn.Execute( SQL )
    ...
    %>
    Last edited by Old Pedant; 08-19-2012 at 10:04 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your example Old Pedant,

    I actually got it sorted before I saw your answer and the way I got it to work was like this:

    Code:
    <%
       Function Get1(course, playerId)
          Get1 = Conn.Execute("SELECT COUNT(*) FROM tbl_res WHERE AMT" & course & "=1 AND PlayerId='"& playerId &"'")(0)
       End Function
    				  
       SET TotalLanes=Conn.Execute("SELECT COUNT(*)*18 AS Lanes FROM tbl_res WHERE PlayerId= '"& strpID &"' ")
    
       Dim Total1 
    
       Total1 = Get1(1, strpID) + Get1(2, strpID) + Get1(3, strpID) + Get1(4, strpID) + Get1(5, strpID) + Get1(6, strpID) + Get1(7, strpID) + Get1(8, strpID) + Get1(9, strpID) + Get1(10, strpID) + Get1(11, strpID) + Get1(12, strpID) + Get1(13, strpID) + Get1(14, strpID) + Get1(15, strpID) + Get1(16, strpID) + Get1(17, strpID) + Get1(18, strpID)
    
       Response.Write ROUND(Total1 / TotalLanes("Lanes"),3) * 100
    %>
    What do you think about this solution?

    /S

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    I think it is terrible.

    You have to make 19 calls to the database to accomplish what I did in 1 call.

    Plus you have to do this once per playerid!

    The performance sucks big time.

    Of course, if this is just for a "toy" site that gets 10 visits per day, then who cares. It works. But it's utterly unsuited for a busy site.

    You can trivially add the count to my code. Example:
    Code:
    SELECT playerid, COUNT(*) * 18 AS lanes
           SUM(
                IIF(amt1=1,1,0) + IIF(amt2=1,1,0) + IIF(amt3=1,1,0) + IIF(amt4=1,1,0) +
                IIF(amt5=1,1,0) + IIF(amt6=1,1,0) + IIF(amt7=1,1,0) + IIF(amt8=1,1,0) +
                IIF(amt9=1,1,0) + IIF(amt10=1,1,0) + IIF(amt11=1,1,0) + IIF(amt12=1,1,0) +
                IIF(amt13=1,1,0) + IIF(amt14=1,1,0) + IIF(amt15=1,1,0) + IIF(amt16=1,1,0) +
                IIF(amt17=1,1,0) + IIF(amt18=1,1,0) 
              ) AS holesInOne
    FROM yourtable
    GROUP BY playerid
    ORDER BY playerid
    And that gets you the statistics for *ALL* players.

    If you truly only wanted the stats for one pleyer, it's trivial to change:
    Code:
    SELECT COUNT(*) * 18 AS lanes
           SUM(
                IIF(amt1=1,1,0) + IIF(amt2=1,1,0) + IIF(amt3=1,1,0) + IIF(amt4=1,1,0) +
                IIF(amt5=1,1,0) + IIF(amt6=1,1,0) + IIF(amt7=1,1,0) + IIF(amt8=1,1,0) +
                IIF(amt9=1,1,0) + IIF(amt10=1,1,0) + IIF(amt11=1,1,0) + IIF(amt12=1,1,0) +
                IIF(amt13=1,1,0) + IIF(amt14=1,1,0) + IIF(amt15=1,1,0) + IIF(amt16=1,1,0) +
                IIF(amt17=1,1,0) + IIF(amt18=1,1,0) 
              ) AS holesInOne
    FROM yourtable 
    WHERE playerid = 'jones'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    wborg (08-19-2012)

  • #7
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks,

    I will absolutley change to the way you did it now when you explained why it was so terrible

    Once again, thank you

    /S


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •