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 Tasks t ON t.Id = tp.TaskId
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
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,
        personTaskProject.Title
    FROM
        People ppl,
        (SELECT COUNT(t.id) TaskCount, tp.PersonId, p.Title
            FROM TaskPersons tp
                JOIN Tasks t
                    ON t.Id = tp.TaskId
                JOIN Projects p
                    ON p.Id = t.ProjectId
            GROUP BY tp.PersonId, p.Title) AS personTaskProject
    WHERE ppl.Id = personTaskProject.PersonId;


SQL Fiddle