Getting the Sum For Each Date

I am writing a program in C# that read and writes to SQLite (System.Data.Sqlite) I have a table that looks like this

UserID  ProdID  Value1  Value2  TIME
------------------------------------------------
1        1      10      1      2011-06-01 10:20:30.333
1        2      20      3      2011-06-01 10:20:30.444
2        1      100     11     2011-06-01 10:20:30.333
2        2      22      32     2011-06-01 10:20:30.444
1        1      20      2      2011-06-01 10:30:30.333
1        2      30      4      2011-06-01 10:30:30.444
2        1      22      22     2011-06-01 10:30:30.333
2        2      33      44     2011-06-01 10:30:30.444
1        1      10      1      2011-06-02 10:20:30.333
1        2      20      3      2011-06-02 10:20:30.444
2        1      11      11     2011-06-02 10:20:30.333
2        2      22      32     2011-06-02 10:20:30.444
1        1      20      2      2011-06-02 10:30:30开发者_JAVA百科.333
1        2      30      4      2011-06-02 10:30:30.444
2        1      22      22     2011-06-02 10:30:30.333
2        2      33      44     2011-06-02 10:30:30.444

I need to get Three things from this for each user.

1) The Last Entry of the day from each product for Value1 and 2 and totaled so its one two values that are the sum of all the products. ie for user 1 on 2011-06-01 it would be Value1=50 and Value2=6.

curently i use:

SELECT * FROM TickData AS a,;
(SELECT USERID, DATE(TIME) AS JUSTDATE, MAX(TIME) AS MTIME;
FROM TickData;
GROUP BY 1,2;
) AS b;
WHERE a.USERID = b.USERID;
AND a.TIME = b.MTIME;
AND STRFTIME('%Y-%m',a.TIME) = STRFTIME('%Y-%m','NOW');
AND a.USERID = 1;

this returns:

UserID  ProdID  Value1  Value2  TIME
------------------------------------------------
1        1      20      2      2011-06-01 10:30:30.333
1        2      30      4      2011-06-01 10:30:30.444

For 2011-06-01. Then i loop through the data and add the values when the dates are the same. Giving me Value1=50 and Value2=6.

2) The Highest Entry for each day of Value1 totaled for all products. ie for user 2 on 2011-06-01 it would be Value1=133.

UserID  ProdID  Value1  Value2  TIME
------------------------------------------------
2        1      100     11     2011-06-01 10:20:30.333
2        2      33      44     2011-06-01 10:30:30.444

3) The Lowest Entry for each day of Value1 totaled for all products. ie for user 2 on 2011-06-01 it would be Value1=44.

UserID  ProdID  Value1  Value2  TIME
------------------------------------------------
2        2      22      32     2011-06-01 10:20:30.444
2        1      22      22     2011-06-01 10:30:30.333

I havent had any luck with 2 or 3 yet.

Does anyone have any sugestions

Ok worked it out my self over the weekend

1)

SELECT sum(value1), sum(value2) FROM TickData AS a,
(SELECT USERID, PRODID, DATE(TIME) AS JUSTDATE, MAX(TIME) AS MTIME
FROM TickData
GROUP BY 1,2,3
) AS b
WHERE a.USERID = b.USERID
AND a.TIME = b.MTIME
AND STRFTIME('%Y-%m',a.TIME) = STRFTIME('%Y-%m','NOW')
AND a.USERID = 1
GROUP BY JUSTDATE

2)

SELECT sum(Mval) FROM TickData AS a,
(SELECT USERID, PRODID, DATE(TIME) AS JUSTDATE,ID, MAX(value1) AS Mval
FROM TickData
GROUP BY 1,2,3
) AS b
WHERE a.USERID = b.USERID
AND a.ID = b.ID
AND STRFTIME('%Y-%m',a.TIME) = STRFTIME('%Y-%m','NOW')
AND a.USERID = 2
GROUP BY JUSTDATE

3)

SELECT sum(Mval) FROM TickData AS a,
(SELECT USERID, PRODID, DATE(TIME) AS JUSTDATE,ID, MIN(value1) AS Mval
FROM TickData
GROUP BY 1,2,3
) AS b
WHERE a.USERID = b.USERID
AND a.ID = b.ID
AND STRFTIME('%Y-%m',a.TIME) = STRFTIME('%Y-%m','NOW')
AND a.USERID = 2
GROUP BY JUSTDATE

Edit: 2 and 3 can be done with one query:

SELECT sum(Mval), sum(Mval2) FROM TickData AS a,
(SELECT USERID, PRODID, DATE(TIME) AS JUSTDATE,ID, MAX(PL) AS Mval,MIN(PL) AS Mval2
FROM TickData
GROUP BY 1,2,3
) AS b
WHERE a.USERID = b.USERID
AND a.TIME = b.MTIME
AND STRFTIME('%Y-%m',a.TIME) = STRFTIME('%Y-%m','NOW')
AND a.USERID = 2
GROUP BY JUSTDATE