CODEWITHSUNDEEP
sqlms-accessms-access-2010
spriore
spriore

Reputation: 613

Access 2010: Count and Distinct Count in same query

I am working on a query in Access 2010 that is intended to aggregate the number of projects and project tasks a person has completed.

For example:

Person    |    Project    |    Project Task
1                1                1
1                1                2
1                2                1
2                1                1
2                1                2

I would like it to return:

Person    |    Project(s)    |    Project Task(s)
1                 2                  3
2                 1                  2
Total             3                  5

As it stands my SQL looks like this:

SELECT Person, Project, Count(Project Task) AS Project Task
FROM table 
GROUP BY Person, Project
ORDER BY Person;

Which returns:

Person    |    Project    |    Project Task(s)
1                 1                  2
1                 2                  1
2                 1                  2
Total             3                  5

Any help would be greatly appreciated.

Upvotes: 1

Views: 143

Answers (1)

isaace
isaace

Reputation: 3429

This should work:

select person, count(project), sum(projectTask) from 
(
    select person,  project, count(projectTask) as projectTask
    from table
    group by person, project
)
group by person   
order by person

Upvotes: 2

Related Questions