Problem solve Get help with specific problems with your technologies, process and projects.

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
    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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.