I have two tables, projects and allocation. The projects table has projectid, projectname, and customerid. The allocation table looks like this:
projectid onsite_offshre associate_id 100809493 on 757845 478463443 of 786534 855787555 579485 573457545 on 785456
I have to count the number of associates who are onsite or offshore, with respect to the projectid equal to the projects table to a particular customerid. Can you give me the query for this?
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.
This was first published in December 2007