need help with select statement with case count 1

[bookouri]

I have a table with column state_assigned
I have a select that shows me counts of all state_assigned
of active employees with hire dates between x and y:

select
nvl(state_assigned, 'Unknown') state_assigned,
count(nvl(state_assigned, 0)) cnt
from emps
where hire_date between '16-NOV-2009' and '28-DEC-2009'
and active_flag='Y'
group by state_assigned
order by state_assigned

This gives me a list of all the state_assigned of active staff like:
Alabama 2
Florida 10
Georgia 5
Texas 1

I need my select to list of all the states in the table and only count the ones with active employees.. so i end up with a list of all state_assigned and if no active
employees have that state_assigned then it gives a count of 0 so my list is like:

Alabama 2
Florida 10
Georgia 5
South Carolina 0
Texas 1
(basically they want the report to always list all the states for formatting purposes even if nobody is assigned)

I think i need to use a case statement in the select but havnt been able to work it out so far.

If somebody could point me in the right direction i would appreciate it?

 

[carp]

bookouri -
The following should sum up all states in your table:

SQL> select nvl(state_assigned, 'unknown'), count(*)
  2  from emps
  3  group by nvl(state_assigned, 'unknown')
  4  order by nvl(state_assigned, 'unknown');

NVL(STATE_ASSIGNED,'UNKNOWN')   COUNT(*)
------------------------------ ---------
colorado                               1
south carolina                         2
unknown                                1

My questions to you are "how is your table laid out?" and its corollary, "how would an unassigned state show up in your table?". It looks to me like you either have a second table you haven't mentioned (states?) that would have states that are not represented in your emps table, or your table name is misleading. Please clear this up and we can probably have you sailing on your way in short order. For now, I'll assume you have a states table, in which case, the following may be what you are after:

First, here's my sample data -

  1* select nvl(state_assigned, 'NULL') from emps;

NVL(STATE_ASSIGNED,'NULL')
------------------------------
south carolina
south carolina
colorado
NULL

SQL> select * from states;

STATE
------------------------------
south carolina
colorado
wyoming

Then the query to join the tables - 

SQL> SELECT nvl(s.state,'unknown'), count(e.rowid)
  2  FROM states s FULL OUTER JOIN emps e
  3  ON nvl(s.state,'unknown') = nvl(e.state_assigned, 'unknown')
  4  GROUP BY nvl(s.state,'unknown')
  5  ORDER BY nvl(s.state,'unknown');

NVL(S.STATE,'UNKNOWN')         COUNT(E.ROWID)
------------------------------ --------------
colorado                                    1
south carolina                              2
unknown                                     1
wyoming                                     0

Let us know if this works for you.

 

[bookouri]

Nope, there's just one table. The table has hundreds of rows. Each row is something like emp_name, hire_date, state_assigned, active_flag. All the states will have had employees assigned at one time or another so all states are represented in the table. So I'm selecting from one table containing data like:
name, hire_date, state_assigned, active_flag
bob 01/01/1999 Alabama Y
bill 01/01/2000 Alaska N
jane 12/12/1999 Alabama Y
jill 01/01/2005 Texas Y

What Im getting with my existing select is:
Alabama 2
Texas 1

What I want to be able to get is:

Alabama 2
Alaska 0
Texas 1

 

[carp]

Aha! Well, then you might try something like this:

SELECT state_assigned AS state, 
       sum(DECODE(active_flag, 'Y',1,0)) AS active_emps
FROM emps
GROUP BY state_assigned
ORDER BY state_assigned;

I haven't tested this yet, but it should get you pretty close.

 

[bookouri]

That is perfect. I was trying to do it with a case instead of decode and just couldnt get the case to work like that.

Thanks, you saved me from another day of struggle on this one. I really appreciate it.

 

[carp]

Just in time for the new year! Glad it worked for you.