EXPERT RESPONSE
The solution is straightforward but there is a
gotcha or two
that should be noted.
select p.projectid
, p.projectname
, a.onsite_offshre
, count(a.associate_id) as associates
from projects as p
left outer
join allocations as a
on a.projectid = p.projectid
where p.customerid = 937
group
by p.projectid
, p.projectname
, a.onsite_offshre
The first gotcha is the status code. From the name "onsite_offshre" it would be reasonable to conclude that there will be two values, corresponding to onsite and offshore. However, the sample data shows a blank value. Perhaps there may also be a NULL in the column, which isn't the same as a blank. So potentially, for every project there could be four associate counts -- onsite, offshore, blank, and NULL.
The other gotcha is that there could be no rows in the associates table for a particular customer. That's why the query uses a LEFT OUTER JOIN. In this case, the onsite_offshre value in the result set produced by the join will be NULL. Luckily, you will still be able to tell whether there were actually no associates for that project, or whether the only associates for that project had a NULL onsite_offshre value. See if you can figure out how to tell the difference.
|
