# Listing employees and their project tasks

My database (SQL Server 2008) looks like this:

People 1 -- * TaskPersons * -- 1 Tasks * -- 1 Project

The GROUP BY part of the query is quite long, all because of the COUNT aggregate. This seems to perform reasonably well. I would like to know if there is a more concise way of writing this.

use Projects
SELECT COUNT(t.id), ppl.Name, ppl.Birthdate, ppl.Title, ppl.Role, ppl.Status, ppl.Warehouse, ppl.StartDate, ppl.SalaryBand, ppl.Salary,p.Title FROM People ppl
JOIN TaskPersons tp On tp.PersonId = ppl.Id
JOIN Projects p ON p.Id = t.ProjectId
GROUP BY ppl.Name, ppl.Name, ppl.Birthdate, ppl.Title, ppl.Role, ppl.Status, ppl.Warehouse, ppl.StartDate, ppl.SalaryBand, ppl.Salary, p.Title

• If you group by project title, and there is a one-to-one correspondence between projects and tasks, then won't the count of tasks be 1 for every row? – 200_success Oct 10 '13 at 3:48
• Thank you, my relationship is listed in correctly, I have revised. – user30586 Oct 10 '13 at 19:55

Try putting GROUP BY in a sub-select.

SELECT  personTaskProject.TaskCount,
ppl.Name,
ppl.Birthdate,
ppl.Title,
ppl.Role,
ppl.Status,
ppl.Warehouse,
ppl.StartDate,
ppl.SalaryBand,
ppl.Salary,
FROM
People ppl,