Mysql complicated query (cannot even describe it) :)

[ktsangop]

Hi everyone, i would like some help with a couple of mysql queries.
I have a table of data which looks like this :

And represents a transaction log for players of a game.

The ID is the primary key (auto incremented).
DATETIME is the date and time of the transaction.
USERNAME is useless. :D
PCNAME is a description of the physical computer in which the player was when the trasaction was made. MAX number of PCs is 30 so values can be PC1-PC30.
PLAYER is the name of the player.
CREDIT_A, B, C, D are game values, all Unsigned integers.
GAMEDAY is a value representing the virtual "in-game" DAY.

What i want to ask the Database is :
Given a list of player names (PLAYER column) , lets say (NICK, PETER), calculate the SUM of each credit type (CREDIT_A,CREDIT_B,CREDIT_C,CREDIT_D) for the last 3 game days (GAMEDAY) and show it seperately for each of those gamedays.
So the resulting table will look like :

Can i achive it somehow?

Thanks in advance!

[pritaeas]

Off the top of my head:

SELECT 
    player, 
    SUM(credit_a) AS SumA, 
    SUM(credit_b) AS SumB, 
    SUM(credit_c) AS SumC, 
    SUM(credit_d) AS SumD, 
    gameday
FROM TransactionLog
WHERE player IN ('NICK', 'PETER')
AND gameday IN (
    SELECT gameday
    FROM TransactionLog 
    ORDER BY gameday DESC
    LIMIT 3
)
GROUP BY player, gameday
ORDER BY player, gameday DESC

If incorrect, am sure it will get you started.

[ktsangop]

Wooow that was fast!!!!

Though i got a weird error at once:
Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

After a fast search i changed it to: (Don't ask me why i wouldn't know!)

SELECT 
    player, 
    SUM(credit_a) AS SumA, 
    SUM(credit_b) AS SumB, 
    SUM(credit_c) AS SumC, 
    SUM(credit_d) AS SumD, 
    gameday
FROM TransactionLog
WHERE player IN ('NICK', 'PETER')
AND gameday IN (
    SELECT * FROM(
        SELECT gameday
        FROM TransactionLog 
        ORDER BY gameday DESC LIMIT 3) 
    alias)
GROUP BY player, gameday
ORDER BY player, gameday DESC

This one worked BUT result set was only for the last gameday so i added the DISTINCT keyword and voila!

SELECT 
    player, 
    SUM(credit_a) AS SumA, 
    SUM(credit_b) AS SumB, 
    SUM(credit_c) AS SumC, 
    SUM(credit_d) AS SumD, 
    gameday
FROM TransactionLog
WHERE player IN ('NICK', 'PETER')
AND gameday IN (
    SELECT * FROM(
        SELECT DISTINCT gameday
        FROM TransactionLog 
        ORDER BY gameday DESC LIMIT 3) 
    alias)
GROUP BY player, gameday
ORDER BY player, gameday DESC

Thanks very much!
I admire the way database developers think. I couldn't figure it out although i know all of the functions you used here...
:)

I have another one quite simpler, but i will try it myself first and then maybe i'll come back with another question!
^_^

[pritaeas]

Ah yes, of course. I forgot about the DISTINCT. There are of course much more gameday's... I have no database here to do a quick test, that explains me forgetting the subselect table alias.

[ktsangop]

I would like a little more help regarding the above query.
:)

I want to add the PCNAME column in the SELECT statement, so as to be able to see in which PC was the player logged in the last time he played.

If i just add the PCNAME in the SELECT statement, it does not correspond to that.
On the contrary, i get a "kind of random" PCNAME in which the player was logged. By random i mean one of the PCs he was logged but not the last one, in respect to the GAMEDAY column.

Is there any way to achieve that?

Below is a query response in accordance to the data table i provided above:

The marked in red cell is wrong. Although NICK was logged both in PC23 and PC16, it was PC16 the last one he did on gameday 5.

So my question is how does mysql pick the PCNAME and how can i change it.

I hope i made it clear enough, thanks in advance!

[pritaeas]

That's because of the group by most likely. You can try to do a left join between what you have and the original table.

[ktsangop]

All right i tried this one :

SELECT 
    t1.PCNAME,
    t1.player, 
    SUM(t1.credit_a) AS SumA, 
    SUM(t1.credit_b) AS SumB, 
    SUM(t1.credit_c) AS SumC, 
    SUM(t1.credit_d) AS SumD, 
    t1.gameday
FROM TransactionLog AS t1 
LEFT JOIN TransactionLog AS t2
    ON t1.PCNAME=t2.PCNAME
WHERE t1.player IN ('NICK', 'PETER')
AND t1.gameday IN (
    SELECT * FROM(
        SELECT DISTINCT gameday
        FROM TransactionLog 
        ORDER BY gameday DESC LIMIT 3) 
    alias)
GROUP BY t1.player, t1.gameday
ORDER BY t1.player, t1.gameday DESC

But it is completely wrong...
:S
The sums are wrong and the PCNAME is shown in the same way as before.

[pritaeas]

Try left join on player and gameday.

[ktsangop]

Tried:

LEFT JOIN TransactionLog AS t2
    ON t1.gameday=t2.gameday AND t2.player=t1.player

But no luck. Same results.

Sorry for being boring!
:S

[pritaeas]

Can you provide an sql script that creates the table and inserts the data from your original post? (I'm not going to type all that.)

[ktsangop]

Of course!
This is the TransactionLog table but the names are quite different.

pcname = PCNum
gameday = Vardia
player = Customer
for credit_a use trans_in and ommit the other SUMS just one is enough to check the validity

[pritaeas]

Is this what you want?

SELECT 
    customer, 
    SUM(trans_In) AS SumA, 
    vardia,
    (SELECT pcnum 
     FROM trans t2 
     WHERE t2.Customer=t1.Customer 
     AND t2.vardia=t1.vardia 
     ORDER BY date DESC LIMIT 1) AS pcnum
FROM trans t1
WHERE customer IN ('Thanos', 'Kostas')
AND vardia IN (
    SELECT * FROM(
        SELECT DISTINCT vardia
        FROM trans 
        ORDER BY vardia DESC LIMIT 3) 
    alias)
GROUP BY customer, vardia
ORDER BY customer, vardia DESC

[ktsangop]

That's it!
I can't thank you enough pritaeas!

Not important now but, as far as i understand this could be done also with LEFT JOIN as you mentioned before right?
I' ll try it myself just for practice... I won't bother you anymore!!!
:)

Thanks again!

[pritaeas]

Yes, you could left join it to a sub-select. I didn't understand when I wrote it that player/game was connected to different users and dates, messing up the result.