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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: