problem with count

[johnny_m]

hi,
i have a problem with a query where im trying to find how many job_titles there are in a column and display in each row in a column defined by me. this is the code i have so far and what i get back in the row 'JOBS' is 1 for every record can any body help???

SELECT Project_Name, emp.Job_Title, MAX(Hours_spent) AS MaxHrs, AVG(Hours_spent) AS AvgHrs, COUNT(DISTINCT emp.Job_Title)AS JOBS
FROM ProjHoursSpentTable ProHrs
INNER JOIN EmployeesTable emp
ON ProHrs.Employee_Number = emp.Employee_Number
INNER JOIN ProjectTable proj
ON proHrs.Project_Number = proj.Project_Number
GROUP BY Project_Name, emp.Job_Title, prohrs.Hours_spent

Table: ProjHoursspentTable
Columns: Employee_Number, Project_Number, Hours_spent

Table: ProjectTable
Columns: Project_Number, Project_Name, budget, start_date

Table: EmployeesTable
Columns: Employee_number, Employee_Name, job_title, address, town, county, extention, manager, joining_date, salary, commission, DepartmentID

In the column JOBS i get 1 for each record

Any help would be appreciated
Thanks

[Fumigator]

The COUNT() column function when used with GROUP BY just counts how many rows have been grouped up and summarized for each row. Using DISTINCT inside COUNT() doesn't change the COUNT() result and does nothing for you. Specifying a column name inside COUNT() as opposed to using COUNT(*) results in not counting rows where that column name is NULL-- that's the only difference.

Since you're including Hours_spent in your GROUP BY clause, your groupings are probably not what you want; you're getting a different group (and hence, a row in your resultset) for every different value in that row.

[johnny_m]

Thanks for your help but does anyone know the solution to the problem????