# Thread: Calculations on fields containing non numerical data?

1. Registered User
Join Date
Dec 2010
Posts
36

## Unanswered: Calculations on fields containing non numerical data?

I am developing a database of soccer transfers. The transfer fee field contains numerical values but also 'u/d' to signify a transfer in which the fee has not been disclosed and 'loan' to signify a loan move. Is it possible to perform calculations on fields containing non numerical data? e.g. could I run a query that replaced u/d and loan with 0 for the purposes of performing calculations or should I remove all non numerical values from the fee field?

I am preparing the data prior to importing it into SQL but you can view the data structure here :

Premier League Transfers Winter 2012

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
That column is really 2 columns...one column should be anount decimal (15,2) and the Other should be type varchar(??)

3. Registered User
Join Date
Dec 2010
Posts
36
I thought about doing that but if a user wants to search for a players history i.e. a list of all the clubs he has played for and the transfer fees paid this scenario is common :

smith joins arsenal in 2006 for €5.00
smith joins bolton in 2007 on loan
smith joins bolton in 2008 for €2.50
smith joins PSG in 2009 for u/d

How would I deal with that?

There's an example here but in their case they have a separate column for loan moves and denoate the fee with - but still have a running total.

Marco Borriello - Player changes, player transfers - transfermarkt.co.uk

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'm with Brett. I'd have two columns, one numeric and one a foreign key to another table describing the type of transfer. The FK data type could be a CHAR to make it simpler, using something like T for transfer, L for loan, and U for undisclosed. You would then have much more flexibility to handle reporting as a side benefit!

-PatP

5. Registered User
Join Date
Dec 2010
Posts
36
Ok. So if I have two columns, one named fee which lists the value of any fee paid and one name type which lists the transfer (disclosed, undisclosed, loan, free). I'm not sure how to construct the query to output the transfer history of a player and give a total of the fees paid. What would be the correct syntax?

Select* from table where player Like 'playername' And type like 'disclosed' And sum fee?!??!

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```CREATE TABLE #Palermo (
player       NVARCHAR(25)    NOT NULL
,  team         NVARCHAR(25)    NOT NULL
,  y            DATETIME        NOT NULL
,  transType    CHAR(1)         NOT NULL
,  amount       MONEY           NOT NULL
)

INSERT INTO #Palermo
SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00

SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
+  CASE transType
WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
WHEN 'L' THEN ' on loan'
WHEN 'U' THEN ' for u/d'
ELSE '????'
END
FROM #Palermo

DROP TABLE #Palermo```
-PatP

7. Registered User
Join Date
Dec 2010
Posts
36
Thanks Pat. Now I know it's possible. I'm collating the data at the moment prior to importing into SQL - 22 excel spreadsheets with 242 worksheets in total. I'll make one table from that data as it makes it easier to perform queries. Once that's done, hopefully sometime over the next week, I'll show you the structure so you'll have a better idea. For this particular query it's not necessary to specify the clubs a player transfers to or the year as that data is already recorded in separate fields.

8. Registered User
Join Date
Dec 2010
Posts
36
Originally Posted by Pat Phelan
Code:
```CREATE TABLE #Palermo (
player       NVARCHAR(25)    NOT NULL
,  team         NVARCHAR(25)    NOT NULL
,  y            DATETIME        NOT NULL
,  transType    CHAR(1)         NOT NULL
,  amount       MONEY           NOT NULL
)

INSERT INTO #Palermo
SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00

SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
+  CASE transType
WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
WHEN 'L' THEN ' on loan'
WHEN 'U' THEN ' for u/d'
ELSE '????'
END
FROM #Palermo

DROP TABLE #Palermo```
-PatP
I've just executed the query and it doesn't give a total of all fees paid. I need to generate a list of the transfers AND give a running total. Below is a screenshot of the structure of a table :

http://soccer-europe.com/Transfers/Table_Structure.jpg

So in mind of the data from the above worksheet if I want to generate a report showing a list of transfers to a club and give a running total of the transfers the first part is :

SELECT*
FROM TableName

and the second?

Basically replicating the sum function in Excel which will give a total regardless of non numerical data.
Last edited by Palermo; 03-01-12 at 14:56.

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
You'll have to adjust the code based on what kind of extract you are running. For players, the code would be:
Code:
```CREATE TABLE #Palermo (
player       NVARCHAR(25)    NOT NULL
,  team         NVARCHAR(25)    NOT NULL
,  y            DATETIME        NOT NULL
,  transType    CHAR(1)         NOT NULL
,  amount       MONEY           NOT NULL
)

INSERT INTO #Palermo
SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00

SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
+  CASE transType
WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
WHEN 'L' THEN ' on loan'
WHEN 'U' THEN ' for u/d'
ELSE '????'
END
,  (SELECT SUM(amount)
FROM #Palermo AS z1
WHERE  z1.player = #Palermo.player
AND z1.y <= #Palermo.y) AS PmtToDate
FROM #Palermo

DROP TABLE #Palermo```
-PatP

10. Registered User
Join Date
Dec 2010
Posts
36
Thanks. Would it be possible to show a total instead of a running total? e.g.

smith joins arsenal in 2006 for €5.00
smith joins bolton in 2007 on loan
smith joins bolton in 2008 for €2.50
smith joins PSG in 2009 for u/d
Total €7.50.

If you don't mind could you explain what the AS statement does?

Is the purpose of trimming the amount to show the values correct to 2 decimal places?
Last edited by Palermo; 03-01-12 at 18:20.

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Based on the last change you've proposed I'm pretty sure that what you are trying to do is a presentation issue, not really a data management issue. This means that we're trying to solve the problem in the wrong place.

For what you are trying to do (sophisticated formatting with the concept of rows, headers, footers, and dynamic formatting) is really an issue that needs to be handled by the presentation tool. This is easier to do within such a tool, and it scales better when you need to support many languges, formats, etc.

A data management tool like SQL Server is intended to store and protect your data. It handles getting data to the database, from the database, ensuring that the data is "correct" in the sense that it is complete and meets the criteria that define "good" versus "bad" data.

A data presentaiton tool like Reporting Services allows you to present reports like what you're suggesting. It allows them to be tailored to the users choice of languge, graphics, etc This tool is where you ought to make the kind of changes that you're addressing now, such as running totals versus group totals.

-PatP

12. Registered User
Join Date
Dec 2010
Posts
36
Ok thanks I'll take a look.

13. Registered User
Join Date
Dec 2010
Posts
36
Originally Posted by Pat Phelan
You'll have to adjust the code based on what kind of extract you are running. For players, the code would be:
Code:
```CREATE TABLE #Palermo (
player       NVARCHAR(25)    NOT NULL
,  team         NVARCHAR(25)    NOT NULL
,  y            DATETIME        NOT NULL
,  transType    CHAR(1)         NOT NULL
,  amount       MONEY           NOT NULL
)

INSERT INTO #Palermo
SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00

SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
+  CASE transType
WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
WHEN 'L' THEN ' on loan'
WHEN 'U' THEN ' for u/d'
ELSE '????'
END
,  (SELECT SUM(amount)
FROM #Palermo AS z1
WHERE  z1.player = #Palermo.player
AND z1.y <= #Palermo.y) AS PmtToDate
FROM #Palermo

DROP TABLE #Palermo```
-PatP
I've tried to adapt this code for the table structure but cannot get it to work. I have the following fields :

Player
Position
Previous Club
New Club
Transfer Fee
Type
League
Window

Values for Type can be :
Disclosed
Undisclosed
Loan
Free

I changed the above code to :

Code:
```SELECT player + ' joins ' + [New Club] + ' in ' + STR(Year(Window), 4)
+  CASE Type
ELSE '????'
WHEN 'Disclosed' THEN ' for €' + LTrim(Str(Transfer Fee, 15, 2))
WHEN 'Loan' THEN ' on loan'
WHEN 'Undisclosed' THEN ' for u/d'
END
,(SELECT SUM(Transfer Fee)
FROM #Summer_2001_2011 AS z1
WHERE  z1.player = # Summer_2001_2011.Player
AND z1.y <= #Summer_2001_2011.y) AS PmtToDate
FROM #Summer_2001_2011```
The Transfer Fee field is money datatype. NULL values are listed when there is no fee e.g. loan or undisclosed transfer.

What am I doing wrong?
Last edited by Palermo; 04-09-12 at 20:04.

14. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Show the complete CREATE TABLE statement for the #Summer_2001_2011 table, and some sample rows of data (like I gave in the previous example) so that I can see if I can help you.

-PatP

15. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579