Q

SQL to count values of a status code

Our SQL expert answers a question about counting values of a status code.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close