SQL count of distinct records against multiple criteria

I have a table in SQL SERVER “usage” that consists of 4 columns

userID, appid, licenseid, dateUsed

Now I need to query the table so that I can find how many users used an app under a license within a time frame. Bearing in mind that even if user 1 used the app on say january 1st and 2nd they would only count as 1 in the result. i.e unique users in the time frame.

so querying

where dateUsed > @from  AND dateUsed < @dateTo

what I would like to get back is a table of rows like

, appid, licenseid

Has anyone any ideas how? I have been playing with count as subquery and then trying to sum the results but no success yet.

Answer

Just tested this and provides your desired result:

SELECT appid, licenseid, COUNT(DISTINCT userID)
FROM usage
WHERE dateUsed BETWEEN @from AND @dateTo
GROUP BY appid, licenseid

Advertisement

Answer