SQL Server CE count group by

I want to create a SQL query that will do the counting for me instead of in the razor code, I want to calculate the amount of distinct dates in my database.

I found that SQL Server CE does not support count(distinct column_name) and instead using group by would be the solution, but for some reason I could not make it work, what am I doing wrong?

SELECT COUNT(date) as date 
FROM Test 
GROUP BY date

This simply counted every date in my database and not the distinct ones when writing it out like the following

var totalcount = 0;

foreach (var c in db.Query(query))
{
    var ttotalcount = c.date;
    totalcount += ttotalcount;
}
<a>@totalcount<a>

Solution

Updated Your query is asking for the counts of each distinct date. If you want to know how many of those you have you need to sum them up. You can do this be nesting your query with a SUM query. Then use an additional column defulted to "1", to allow to sum up the number of rows (which should be your distinct dates). Also date can be a reserved word. You might want to try and avoid using that as a column name.

SELECT SUM(New_Row) as dateSUM from (
   SELECT COUNT(date) as dateCount, 1 as New_Row FROM Test GROUP BY date
) a