Hi,

any 1 can help me.. sql statement

Table User
userlogin|username|
---------|--------|
ken |Ken |
lam |Ah Lam |
man |Ah Man |

Table Game
game_name|gamedetail |
---------|--------------|
3fun |play for fun |
twinbee |shooting game |
1943 |1943 |

Table Score
userlogin|score|game_name|playdatetime|
---------|-----|---------|------------|
ken | 4|3fun |2004-01-01 |
ken | 2|twinbee |2004-01-02 |
ken | 1|3fun |2004-03-01 |
ken | 2|twinbee |2004-03-02 |
ken | 3|twinbee |2004-03-11 |
ken | 4|3fun |2004-03-11 |
ken | 0|3fun |2004-03-12 |
ken | 3|twinbee |2004-03-12 |
ken | 4|1943 |2004-03-15 |
ken | 4|1943 |2004-03-17 |


Result Table
Ken's Stat

|Last 3 days |Last 7 days |All |
|---------------------|---------------------|---------------------|
|Play Count|Game Count|Play Count|Game Count|Play Count|Game Count|
score=4 | 2| 1| 3| 2| 4| 2|
score=3 | 0| 0| 2| 1| 2| 1|
score=2 | 0| 0| 0| 0| 2| 1|
score=1 | 0| 0| 0| 0| 1| 1|
score=0 | 0| 0| 0| 0| 1| 1|


thx first

What exactly do you need to get out of the query?

I want to gruop the records by 2 fields, score and playdatetime

finally I solved the problem.

Thanks anyway

set @now:=now();
set @cut1:=date_add(@now, interval -3 day);
set @cut2:=date_add(@now, interval -7 day);
set @userlogin:='buyer';
select
score,
playdatetime > @cut1 as d1,
playdatetime > @cut2 && playdatetime <= @cut1 as d2,
playdatetime <= @now as d3,
count(*) as playcount,
count(distinct(game_name)) as gamecount
from TableScore
where userlogin = @userlogin
group by score,
playdatetime > @cut1,
playdatetime > @cut2 && playdatetime <= @cut1,
playdatetime <= @now;