Hi,
**Question:**
I would like to calculate the winAmount and POP for each of the betAmount values, but some of the data is only available in the 'pre purchase bal' and rest is available in 'completed final bal'.
**Sample data:**
```
event,betAmount,winAmount,endBalance
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.0,299.75
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.5,300.0
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.0,299.75
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.6,299.35
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.2,298.55
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.6,298.15
```
**Query:**
```
SELECT purchasesbyba,
wager,
betamount,
purchasesbyba,
winamount,
(winamount+ pw) / wager * 100.0 AS pop
FROM (
SELECT event,
count(
CASE
WHEN(
event IS 'Game Completed Final Balance') THEN 1
ELSE NULL
END) AS 'purchases',
sum(mga.betamount) AS wager,
sum(mga.winamount) AS winamount,
sum(mga.progressivewin) AS pw,
count(
CASE
WHEN(
event IS 'Game Completed Final Balance'
AND winamount != 0.0 ) THEN 1
ELSE NULL
END) AS wins,
betamount,
count(betamount) purchasesbyba
FROM mgaccounting AS mga
GROUP BY betamount );
```
**Results so far:**
```
purchasesbyba,wager,betamount,purchasesbyba,winamount,pop
0,,,0,6637.20000000004,
2236,559.0,0.25,2236,,
1141,570.5,0.5,1141,,
1316,1316.0,1.0,1316,,
1278,1917.0,1.5,1278,,
1123,2807.5,2.5,1123,,
```
Thank you for any pointers!