Why does this mysql query give me garbage results?

Im trying to get the total amount of points a user has, as well as current month's points. When a user gets a point, it gets logged into the points table with a timestamp. Totals ignore the timestamp, while the current month's points looks for the points with the correct timestamp (from the first day of the month).

SELECT user_id, user_name, sum(tpoints.point_points) as total_points, sum(mpoints.point_points) as month_points
FROM users 
LEFT JOIN points tpoints
ON users.user_id = tpoints.point_userid 
LEFT JOIN points mpoints 
ON (users.user_id = mpoints.point_userid AND mpoints.point_date > '$this_month')
WHERE user_id = 1
GROUP BY user_id

points table structure

 CREATE TABLE IF NOT EXISTS `points` (
  `point_userid` int(11) NOT NULL,
  `point_points` int(11) NOT NULL,
  `point_date` int(11) NOT NULL,
  KEY `point_userid` (`point_userid`),
  KEY `point_date` (`point_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This results in a very large number, thats equal to the sum of all points, multiplied by the number of rows that match the query.

I need to achieve this without the use of subqueries or multiple queries.

From stackoverflow

• SELECT user_id, user_name, 
         (
         SELECT  SUM(points.point_points)
         FROM    points
         WHERE   points.point_userid = users.user_id
         ) AS total_points,
         (
         SELECT  SUM(points.point_points)
         FROM    points
         WHERE   points.point_userid = users.user_id
                 AND points.point_date > '$this_month'
         ) AS month_points
  FROM   users 
  WHERE  user_id = 1
  

  Yegor : Forgot to mention that I need to do this without the use of subquries.

  Joel Coehoorn : Why? That seems like a silly constraint. I suppose you could convert it to a self-join, but still.

  Quassnoi : Don't see what's bad in subqueries, but if you cannot use them, then I'm afraid you will have to use user defined functions. You cannot do it with pure relational operations.

  Yegor : I see. In your example thou, its actually 3 queries. Would I be better off getting the user data + totals with 1 query, and the month's points with a second separate query?

  sfossen : @vartec: I think think we're missing the point that joins are needed, it's a single table that needs conditional sums.

• try

  SELECT user_id, user_name, sum(point_points) as total_points, sum( case when point_date > '$this_month' then point_points else 0 end ) as month_points
  FROM users
      LEFT JOIN points
          ON users.user_id = points.point_userid 
  WHERE user_id = 1
  GROUP BY user_id, user_name
  

  Quassnoi : You still need a join with points here :)

  sfossen : you are right :P

  Yegor : Indeed he is. :)

  sfossen : and now it's there :)

  Quassnoi : And wrap the point_points with COALESCE(..., 0), so he can get zero sums if there are no points at all.

  glavić : +1 vote up for SUM(CASE WHEN ....) part

  Yegor : You are the man!