Sport scoring log system - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Sport scoring log system

Author

Topic

Hannes
Starting Member

1 Post

Posted - 2008-11-20 : 15:48:32

ID Date Home Away Pool H1s A1s H1b A1b
1 2008/03/01 President Heidelberg 1 34 10 5 0
2 2008/03/01 Goudrif Vereeniging1 19 3 4 0
3 2008/03/01 Alberton Randburg 1 21 10 4 0
4 2008/03/08 Goudrif President 1 8 32 0 5
5 2008/03/08 Heidelberg Alberton 1 22 30 1 5
6 2008/03/08 Randburg Vereeniging1 34 0 5 0
7 2008/03/15 President Randburg 1 8 3 4 1
8 2008/03/15 Heidelberg Goudrif 1 23 0 4 0
9 2008/03/15 VereenigingAlberton 1 7 25 0 5
10 2008/03/18 VereenigingPresident 1 8 8 2 2
11 2008/03/18 Alberton Goudrif 1 26 0 5 0
12 2008/03/18 Randburg Heidelberg 1 15 22 1 4
13 2008/04/19 Alberton President 1 18 24 1 4
14 2008/04/19 VereenigingHeidelberg 1 7 22 0 4
15 2008/04/19 Randburg Goudrif 1 15 10 4 1

Hi I want to show a score log to our school.

The table above shows a series of games played. To determine the winner.
You add all the points scored by a school and subtract the opponents score to determine the difference.
You add all its bonus points and the winner is the one with the highest bonus points. If there is a draw
the one with the highest points difference wins.

My problem lies in the same school sometimes plays at Home and others Away. Not only that but
it has a Home score(H1s) and Away score(A1s) as well as a Home bonus(H1b) and Away bonus(A1b)

SELECT Home, sum(H1s), sum(H1b)
FROM [Sup62008].[dbo].[08B1]
where Pool = 1
Group by Home
UNION
SELECT Away, sum(A1s) as H1s, sum(A1b)as H1b
FROM [Sup62008].[dbo].[08B1]
where Pool = 1
Group by Away

Gives me
Home (No column name) (No column name)
Alberton 55 10
Alberton 65 10
Goudrif 10 1
Goudrif 27 4
Heidelberg 45 5
Heidelberg 54 8
President 42 9
President 64 11
Randburg 13 1
Randburg 64 10
Vereeniging 3 0
Vereeniging 22 2

Can someone give me a result of
Alberton 120 20
President 106 20
Heidelberg 99 13
Randburg 77 11
Goudrif 37 5
Vereeniging 25 2

Thanks I am using SQL 2005

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-11-20 : 16:34:34

why is your table named [08B1] ?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources