Conditional Sum

Another question one of my students has sent me. This question tests your understanding of SQL Group Functions . Given the following betting activity table (bet_tbl table) :

As you can see, each row represents one placed bet, and includes the following fields:

• betID – bet number
• CustomerID – customer number
• betDate – the date in which this bet was made
• betOutcome – the bet outcome, 0 represents a loss and 1 represents a win.
• amount – the amount of money the customer has lost or won.

First task – Create a query to display the biggest loss, and the biggest win for each customer:

Second Task – Create a query to display the overall amount of win / loss for each customer:

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)

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