Last post Apr 25, 2013 04:35 PM by prog4fun
Apr 24, 2013 12:20 PM|prog4fun|LINK
I have a table with the statistics of runs made by different runners and another table with their Profile's information like the Birthday. I can actually count how many runs made by all the runners into a specific age group with this code:
var RunsPerAge= db.Query("SELECT * FROM Stats s LEFT JOIN UserProfile u ON s.userId = u.UserId WHERE u.Birthday> @0 AND u.Birthday<= @1 ORDER BY dateOfRun DESC", dateMIN, dateMAX);
var nbRuns= RunsPerAge.Count();
But now, I have a hard time when I want to count how many distinct userId of runner I have. I have tried different combination of "distinct", "count", "group by" but without success. Do you have an idea?
Apr 24, 2013 12:29 PM|kaushalparik27|LINK
SELECT COUNT(userId) FROM STATS GROUP BY userId
Apr 24, 2013 12:33 PM|kaushalparik27|LINK
And to retrieve all userProfile information along with COUNT you may use CTE.. below is something similar algorithm on how you can go for it:
WITH COUNT_CTE (count_num,userid)
AS (SELECT COUNT(userId),userId FROM STATS_TABLE GROUP BY userId)
SELECT COUNT_CTE.*, UserProfile.*
FROM UserProfile INNER JOIN COUNT_CTE ON UserProfile.userId = COUNT_CTE.userid
GROUP BY UserProfile.userId
ORDER BY UserProfile.userId
hope i understood your requirement./.
Apr 25, 2013 04:50 AM|Afzaal.Ahmad.Zeeshan|LINK
Ummm according to me, you are going good. Are you having correct database opened?
You can try the query to see whether does it provide you with the accurate result? Then use
You are going well. Just see the query, it might have some errors.
Apr 25, 2013 04:35 PM|prog4fun|LINK
Thanks everyone !
I finally found the right match of distinct and count. Here the solution:
var UserList = db.Query("SELECT DISTINCT (s.userId) FROM Stats s LEFT JOIN UserProfile u ON s.userId = u.UserId WHERE u.Anniversaire > @0 AND u.Anniversaire <= @1 GROUP BY s.userId", dateMIN, dateMAX);
var NbDistinctUserInTheGroup= UserList.Count();