3
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ 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? \$\endgroup\$ – 200_success Oct 10 '13 at 3:48
  • \$\begingroup\$ Thank you, my relationship is listed in correctly, I have revised. \$\endgroup\$ – user30586 Oct 10 '13 at 19:55
3
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ I did not test this to see if there was any performance difference, but it definitely looks cleaner. \$\endgroup\$ – user30586 Oct 28 '13 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.