group and count

[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

 

[tina]

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

 

[arno schoblocher]

hi tina,

thank you, this will work!

arno