SQLServer group by date

Question

I have a problem, for school we continue working on a project another group started last semester. The first table: testresult4values

The next table: event

The third and last table: sec_user

If I run this query:

SELECT sec_user.accountid, MIN(sec_user.firstname) as firstname, MIN(sec_user.lastname) as lastname, convert(date, event.starttime) as startdate, sum(cast(value as int)) as value , sum(cast(value2 as int)) as value2, value3, value4
FROM testresult4values as result
JOIN event
ON result.eventid = event.eventid   
JOIN sec_user
ON result.userid = sec_user.accountid
group by value3, value4, sec_user.accountid, starttime

I get the following output:

As you can see in line 8,9 and 10 the person with accountid 32 has 3 outputs, when I only want 2. I know why this is, it is because he is involved in 2 events on the same date. This is happening because of connection errors and there is nothing we can do about that. The priority is on other parts of the application. What I want is to combine those 2 rows with the same date so the result is:
8| 32| firstname | lastname | 2017-12-13 | 16 | 22
9| 32| firstname | lastname | 2018-03-09 | 18 | 30

Thanks in advance!

Answer 1

GROUP By startdate not starttime and you should get your expected results.

SELECT sec_user.accountid, MIN(sec_user.firstname) as firstname, MIN(sec_user.lastname) as lastname, convert(date, event.starttime) as startdate, sum(cast(value as int)) as value , sum(cast(value2 as int)) as value2, value3, value4
FROM testresult4values as result
JOIN event
ON result.eventid = event.eventid   
JOIN sec_user
ON result.userid = sec_user.accountid
group by value3, value4, sec_user.accountid, convert(date, event.starttime)

Answer 2

You have to GROUP BY your date with the time removed. Swap starttime in your grouping with:

GROUP BY convert(date, event.starttime)

Right now you are seeing the date with the time removed because of your select, but the lines aren't rolling up because time is being considered in the grouping.