# SQLite Forum

Help calculating data when answers in previous row

# Help calculating data when answers in previous row

### (1) By ThanksRyan on 2021-07-29 06:00:46 [source]

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!

### (2) By ThanksRyan on 2021-07-29 13:27:53 in reply to 1 [link] [source]

A simpler question that may lead me on to the next path:

How can I sum the winAmount and count(betAmount) for a given betAmount?

Do I need to use window/lag functions for this?

### (3) By Rico Mariani (rmariani) on 2021-07-30 13:59:07 in reply to 1 [link] [source]

``````create table data (
type text,
betAmount real,
winAmount real,
endBalance real
);

delete from data;

insert into data values
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.5,300.0),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,299.35),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.2,298.55),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,298.15);

-- assumes it's always pairs
with fuse2(t1, b1, w1, e1, t2, b2, w2, e2) as (
select
type,
betAmount,
winAmount,
endBalance,
lag(type) OVER win1,
lag(betAmount) OVER win1,
lag(winAmount) OVER win1,
lag(endBalance) OVER win1
from data
window win1 as (rows 1 preceding)
),
unified_rows(bet, win, end) as (
select b2, w1, e1 from fuse2
where fuse2.t1 = 'Game Completed Final Balance'
)
select bet, count(bet), sum(win) from unified_rows
group by bet;
``````

Results:

``````0.25|3|0.5
1.0|3|1.4
``````

Note: this kind of thing

``````count(
CASE
WHEN (event IS 'Game Completed Final Balance') THEN 1
ELSE NULL
END)
``````

Is all kinds of redundant. Any time you have a CASE expression where the results are 0 or 1 you can pretty much replace it with an equivalent boolean that's more economical.

You never have to write `ELSE NULL` that's the default value if there is no `ELSE`.

The above could simply be

``````count(event IS 'Game Completed Final Balance')
``````

But in any case I don't think you need to go there, see above.

### (4) By ThanksRyan on 2021-08-04 19:55:46 in reply to 3 [link] [source]

Rico,

This is beautiful. Thank you for your help and hand-holding through this exercise.

### (5) By anonymous on 2021-08-04 22:43:36 in reply to 3 [link] [source]

`window win1 as (rows 1 preceding)`

I think this is incorrect. The frame specification is not relevant for the `lag` function, which always uses the previous row, so "`rows 1 preceding`" is redundant. However, I think that you do need to specify the order.

`count(event IS 'Game Completed Final Balance')`

This is not right either, I think. In this case, the `IS` expression results 0 or 1 (never null), so `count` will count all of the rows. What should be needed here is `sum` or `total`, not `count`.