Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

1. ## 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. 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?

3. 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. 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 )
...
%>```

5. 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
%>```

/S

6. 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'```

7. ## Users who have thanked Old Pedant for this post:

wborg (08-20-2012)

8. 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
•