Select from mysql

Question

I have a table like below:

uid nid     points  date                        reason
36  116     2       2012-08-28 11:52:12         session
31  110     2       2012-08-23 15:47:47         session
36  115     2       2012-08-27 11:52:48         session

as u can see uid is not unique, it can be repeated. What i need is to select sum of points for every id between date(30 days before). For example: sum of uid36 = to 4. What i have tried:

$start_time = mktime(0, 0, 0, $today["mon"], ($today["mday"] - 30), $today["year"]);//30 days before
$end_time = time();
$query = db_query("SELECT uid,sum(points), date FROM users_history WHERE date BETWEEN '$start_time' AND '$end_time'");

but how to select for every id

xdazz · Accepted Answer · 2012-08-28 09:20:34Z

4

You need GROUP BY uid, and date is meanless in your query if you are using aggregate function SUM.

SELECT uid, SUM(points) 
FROM users_history 
WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 day) AND NOW() 
GROUP BY uid

And if there is no future date, then you only need

WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 day)

edited Aug 28, 2012 at 9:20

answered Aug 28, 2012 at 9:15

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eggyal Over a year ago

Also beware that $start_time and $end_time will either need to be passed through FROM_UNIXTIME() or else formatted as suitable literals for MySQL (e.g. using date()).

Sundar G · Accepted Answer · 2012-08-28 09:18:14Z

1

for that you need to group your uid.
SELECT uid,sum(points), date FROM users_history WHERE date BETWEEN '$start_time' AND '$end_time'" group by uid;

answered Aug 28, 2012 at 9:18

Sundar G

1,0661 gold badge11 silver badges29 bronze badges

Comments

Sadat · Accepted Answer · 2012-08-28 09:30:05Z

0

SELECT uid, SUM(points) FROM users_history WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() GROUP BY uid

answered Aug 28, 2012 at 9:30

Sadat

3,4812 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

Select from mysql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related