group and count

  • Thread starter Thread starter arno schoblocher
  • Start date Start date
A

arno schoblocher

hello!

i would like to know how i can count within a group by-query. here, i would
like to sum the hours worked on a project and my problem is to find out how
many different employees worked on that project.

i have the following table:

project employee hours
11111 AAA 5
11111 BBB 3
11111 AAA 5
22222 AAA 6
22222 AAA 10

my query so far would be:
select project, sum(hours) from mytable group by project

this will give me project and hours. how can i add the number of employees
like shown below?

project hours number_of_employees_on_project???
11111 13 2????
22222 16 1????


my problem is that count(employee) will give me the total number of records
and not the number of different employees.

thank you

arno
 
the quickest solution i can think of (i usually use the
QBE grid in query design, rather than the SQL pane), is:
create a totals query that groups by project, then groups
by employee, then sums hours.
then use that query as the source for a second totals
query, this time group by project, then count employees,
then sum hours.
hth
 
Back
Top