I need help with SELECT statement

[siatki]

hello,
At the beginning I would like to describe my very simple table. It is
composed of three columns: 'name', 'forename', 'status'. This database
stores results of games. The values of column 'status' can be only: 1 for
win, 2 for lose,0 for draw. My aim is to get a table which consists also 3
columns, but these should be: 'name', 'forename', 'balance'. Of course
balance should display values: number of wins - number of loses for each
player. So the new table should be the agreggate of the same players which
their balance of games. Is there anyone who could create that statement? I
think that it is not very difficult for someone who knows the SQL. If it is
needed the DBMS which I use is MSSQL Server(MSDE). I can not speak and write
english very well so sorry for mistakes. Thanks and greetings for YOU.

[--CELKO--]

The design is wrong. You need to all the attributes (results) into a
single row for each entity (person)

CREATE TABLE Games
(first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
win_cnt INTEGER NOT NULL,
lose_cnt INTEGER NOT NULL,
draw_cnt INTEGER NOT NULL,
PRIMARY KEY (first_name, last_name));

Load the new table with this statement:

INSERT INTO Games
SELECT first_name, last_name,
SUM(CASE WHEN status = 1
THEN 1 ELSE 0 END) AS win,
SUM(CASE WHEN status = 2
THEN 1 ELSE 0 END) AS lose,
SUM(CASE WHEN status = 0
THEN 1 ELSE 0 END) AS draw
FROM Foobar
GROUP BY first_name, last_name;

Now your query is easy.