Nested conditional statements on Mysql

Question

I'm developing a query on phpmyadmin mysql in which I intend to display the running balance of a column say 'CurrentBalance'

The running balance of this column depends when the Activity is a deposit (+), a withdraw (-) , bet (-), payout (+)

What I come up is this

SELECT CONCAT("Trans ID ",`TransactionID`) AS ID, 
`DateTime`,`Type` AS Activity, `Amount`, 0 AS Payout, 
CASE WHEN (SELECT Activity) = "deposit" THEN `Amount`+ `playerdb`.`CurrentBalance`
ELSE CASE WHEN (SELECT Activity) = "withdraw" OR (SELECT Activity) = "bet"
THEN CASE WHEN (SELECT Payout) >0 THEN (SELECT Payout) + `playerdb`.`CurrentBalance`
ELSE `Amount` - `playerdb`.`CurrentBalance` END END END AS CurrentBalance 
FROM `transactiondb` LEFT JOIN `playerdb` ON 
`transactiondb`.`PlayerID` = `playerdb`.`PlayerID` 
WHERE `transactiondb`.`PlayerID`=10078 UNION ALL 
SELECT CONCAT("Bet ID ",`BetID`),`DateTime`,"Bet", `BetAmount`,`Payout`, (SELECT CurrentBalance) FROM `betdb` WHERE `PlayerID`=10078 ORDER BY `DateTime`

The Idea http://postimg.org/image/x3fsxq2qz/

Doing the (SELECT CurrentBalance) on the 2nd SELECT statement yields this error

1054 - Unknown column 'CurrentBalance' in 'field list'

I need to get the CurrentBalance of the previous record so I tried using the alias

Is that possible?

It would be best, if you could provide sample data and the desired result. — fancyPants, Sep 15 '14 at 08:49
Hi, Please take a look the screenshot here at this link http://postimg.org/image/x3fsxq2qz/ — sreifss, Sep 15 '14 at 08:59
That seemed the way to reuse Alias I learned it here http://stackoverflow.com/questions/2077475/using-an-alias-in-sql-calculations — sreifss, Sep 16 '14 at 04:15

score 1 · Answer 1 · answered Sep 15 '14 at 09:14

This is a "teach a man to fish..." answer, since your question is not yet totally clear to me.

In general you can access the previous row with variables.

Have a look at this example:

SELECT
t.*
, @running_total := IF(@foo != foo, 0, @running_total + bar)
, @foo := foo
FROM a_table t
, (SELECT @running_total := 0, @foo := NULL) variable_initialization_subquery
ORDER BY foo;

As the subquery alias suggests, here

, (SELECT @running_total := 0, @foo := NULL) variable_initialization_subquery

we initialize the variables.

We ORDER BY foo because there's no reliable order in data in a database when you don't specify it clearly.

Then the SELECT clause is considered with one column after another. Here the order of the columns is important, too.

First we do this:

, @running_total := IF(@foo != foo, 0, @running_total + bar)

This calculates a running total for every foo. When the foo changes, the running total is reset to 0. The IF() function works like IF(<boolean condition>, <then>, <else>).
Here the variable @foo still has the value of the previous row. The value of the current row is assigned in this line:

, @foo := foo

I hope you get the idea, feel free to ask if anything is unclear. Oh, and you don't need those selects in the case...when... parts.

score 0 · Answer 2 · answered Sep 15 '14 at 11:11

Just guessing... giving the man a fish - might be the wrong fish

 SELECT CONCAT("Trans ID ",TransactionID) ID
      , DateTime
      , Type Activity
      , Amount
      , 0 Payout
      , CASE WHEN Activity = "deposit" 
             THEN Amount + playerdb.CurrentBalance
             WHEN Activity IN("withdraw","bet")
             THEN 
                  CASE WHEN Payout >0 
                       THEN Payout + playerdb.CurrentBalance
                       ELSE Amount - playerdb.CurrentBalance 
                       END 
             END CurrentBalance 
   FROM transactiondb 
   LEFT 
   JOIN playerdb 
     ON transactiondb.PlayerID = playerdb.PlayerID 
  WHERE transactiondb.PlayerID = 10078 
  UNION ALL 
 SELECT CONCAT("Bet ID ",BetID)
      , DateTime
      , "Bet"
      , BetAmount
      , Payout
      , CurrentBalance 
   FROM betdb 
  WHERE PlayerID = 10078 
  ORDER 
     BY DateTime;

Thank you for your time reading this entry.MySql says "#1054 - Unknown column 'Activity' in 'field list'" — sreifss, Sep 16 '14 at 04:20
In field list? No, I think my field list is ok. Maybe you mistyped? Ah, but it's possible that it's referring to 'WHEN activity'. Perhaps that should be 'WHEN type' — Strawberry, Sep 16 '14 at 07:18

Nested conditional statements on Mysql

2 Answers2