Need help with a MYSQL query grouping

Question

Table schema is set up something like this:

userID      Points       timestamp

1           40
3           20
1           10
4           15
3           5

Need to be able to present a report that shows the following:

Total Points Allocated for the Day (0 if none allocated), (nice to have) To what userID's the points were allocated to for that day

I tried the following and well as you can see is not correct

SELECT uid, DATE(time_stamp) AS date, SUM(points) AS total_points
FROM table
GROUP BY date

Answer 1

Assuming you had values as the timestamp, and I'll use the column names userid, points and time_stamp and a table name of userpoints:

SELECT userID,
       sum(points),
       date(timestamp) as date
FROM   userpoints
GROUP BY userID, date(timestamp)

So:

userID      Points       timestamp 

1           40           18-8-2010 12:00:00.000
3           20           18-8-2010 12:00:00.000
1           10           18-8-2010 12:00:00.000
4           15           18-8-2010 12:00:00.000
3           5            18-8-2010 12:00:00.000

Would result in:

userid      points       date
1           50           18-8-2010
3           25           18-8-2010
4           15           18-8-2010

UPDATE: Refined for UNIX Issue

SELECT userID,
       sum(points),
       from_unixtime(timestamp, 'DD-MM-YYYY') as date
FROM   userpoints
GROUP BY userID, date(timestamp)

Answer 2

SELECT DATE(time_stamp) as date1, userID, SUM(Points) as total_points
FROM table1
GROUP BY date1, userID
WITH ROLLUP

A record with null userID and not null date1 shows how many points were allocated at that date, A record with null userID and null date1 - total points allocated.

Answer 3

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick to get a list of days in order to LEFT JOIN against for seeing days where there's zero relations attributed.

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of times, increasing the months based on the NUMBERS.id value:

   SELECT x.*
     FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY)
             FROM numbers n) x
   

4. LEFT JOIN onto your table of data based on the datetime portion:

      SELECT up.userid,
             COALESCE(SUM(up.points), 0) AS points
             DATE(x.dt) AS date
        FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH) AS dt
                FROM numbers n) x
   LEFT JOIN USERPOINTS up ON DATE(FROM_UNIXTIME(up.timestamp)) = DATE(x.dt)
    GROUP BY up.userid, DATE(x.dt)