CODEWITHSUNDEEP
sqlms-access
wra
wra

Reputation: 257

Access SQL: Having clause

I am trying to get a distinct count of users who have a repeat rate greater than one (in Excel for example I would use the countif formula to do this).

I am using Ms-access (2016) and can't seem to get this to work. The results of the following query gives me the same results for the count of userName and Repeat Rate.

SELECT host, department, count([userName]) AS ['Distinct Users'], sum(Logins) AS ['Total Logins'], count([Repeat Rate]) AS ['Repeat Users']
FROM (SELECT DISTINCT department, [userName], host,[Repeat Rate], Logins FROM Sheet1)  AS x
GROUP BY department, host
ORDER BY host, department,
HAVING COUNT([Repeat Rate]) > 1;

Any help is greatly appreciated, thanks!

edit:

+------+----------+------------+-------------+--+
| host | username | Department | Repeat Rate |  |
+------+----------+------------+-------------+--+
| x    | Kyle     | D1         |           1 |  |
| x    | Max      | D1         |           4 |  |
| x    | Will     | D1         |           2 |  |
| x    | Will     | D1         |           2 |  |
+------+----------+------------+-------------+--+

With the above table for example, I would want it to say for host x in Department D1, there are 3 distinct users, and 2 users with a repeat rate greater than 1.

+------+------------+----------------+-------------+
| host | Department | Distinct Users | Repeat Rate |
+------+------------+----------------+-------------+
| x    | D1         |              3 |           2 |
+------+------------+----------------+-------------+

Upvotes: 2

Views: 1169

Answers (3)

Eid Morsy
Eid Morsy

Reputation: 966

Try this

SELECT host, department, count([userName]) AS ['Distinct Users'], sum(IIF([Repeat Rate]>1,1,0)) AS ['Repeat Users']
FROM (SELECT DISTINCT host, department, [userName], [Repeat Rate] FROM Sheet1)  AS x
GROUP BY department, host;

Upvotes: 2

jack6e
jack6e

Reputation: 1522

You might try something like this:

SELECT x.host, x.department, max(users.unique_users) AS ['Distinct Users'], 
    sum(Logins) AS ['Total Logins'], max(repeats.unique_repeats) AS ['Repeat Users']
FROM (SELECT DISTINCT department, [userName], host,[Repeat Rate], Logins FROM Sheet1)  AS x
left join (select host, department, count(userName) as unique_users from (select distinct 
    host, department, [userName] from Sheet1)) as users on users.host = x.host and 
    users.department = x.department
left join (select host, department, count(userName) as unique_repeats 
    from (select distinct host, department, [userName] from Sheet1 where [Repeat Rate] > 1)) 
        as repeats on users.host = x.host and users.department = x.department
GROUP BY department, host
ORDER BY host, department;

Counting unique values is difficult in Access SQL. Normally you would use count(distinct var) as the other suggest, but that is not available to you.

Upvotes: 1

George Lords of Castle
George Lords of Castle

Reputation: 1697

I'm not sure about the join you have shown in your post first part, but, considering your example only, the following query seems to resolve your problem


SELECT HOST, DEPARTMENT, SUM(DISTINCT_USER) AS DISTINCT_USER, MAX(CNT) AS REPEAT_RATE
FROM (
    SELECT HOST, DEPARTMENT, COUNT(DISTINCT USERNAME) AS DISTINCT_USER, REPEAT_RATE, COUNT(*) AS CNT
    FROM 
    GROUP BY HOST, DEPARTMENT, REPEAT_RATE
) A
GROUP BY HOST, DEPARTMENT

Upvotes: 0

Related Questions