ניתוח רווח והפסד

CREATE TABLE bet_tbl
(betID int,
 customerID int,
 betDate date,
 betOutcome bit,
 amount int)

INSERT INTO bet_tbl
VALUES (1,1,getdate(),0,500 ) 

INSERT INTO bet_tbl
VALUES (2,1,getdate(),0,200 ) 

INSERT INTO bet_tbl
VALUES (3,1,getdate(),1,1500 ) 

INSERT INTO bet_tbl
VALUES (4,1,getdate(),0,300 ) 

INSERT INTO bet_tbl
VALUES (5,2,getdate(),0,350) 

INSERT INTO bet_tbl
VALUES (6,2,getdate(),1,200 ) 

INSERT INTO bet_tbl
VALUES (7,2,getdate(),1,20 ) 

INSERT INTO bet_tbl
VALUES (8,2,getdate(),0,600)

[/sourcecode]

SELECT  customerID ,
        MAX(CASE WHEN betOutcome=0 THEN amount ELSE 0 END) AS 'Highest loss',
		MAX(CASE WHEN betOutcome=1 THEN amount ELSE 0 END) AS 'Highest profit'
FROM bet_tbl
GROUP BY customerID 

SELECT  customerID ,
		SUM(CASE WHEN betOutcome=0 THEN amount*-1 ELSE amount END) AS 'Total'
FROM bet_tbl
GROUP BY customerID