Get zero values of the table in the result using CASE

Question

I have the following table:

      State           Soccer players     Tennis players
        CT                   0                 0        
        IL                   5                 10
        IN                   3                  8
        MI                  12                 14
        OH                   8                  9
        AR                   2                  2
        KS                   14                 16
        AL                   8                  7
        CA                   1                  13 
        NV                   2                   3

I would like to form an output table like the one shown below

Region         Total_players
East               0
MidWest            60
SouthWest          34
West               29
SouthEast          0

I am trying to get the result with East Region as well which doesn't have any players. However, in my result set I am not getting "East".

I tried the following query which does not yield the "East" Region.

select CASE 
        WHEN STATE IN ('AL','FL','GA','KY','LA','MS','NC','SC','TN') THEN 'SE'
        WHEN STATE IN ('IL','IN','MI','OH','WI') THEN 'MW'
            WHEN STATE IN ('AR','KS','MO','OK','TX') THEN 'SW'
            WHEN STATE IN ('CT') THEN 'E'
            WHEN STATE IN ('CA','NV') THEN 'W'
            ELSE 'Error'
        END AS Region,
        COUNT(*) as Total,
        from players WHERE TRUNC(t.date) >= to_char(to_date(?,'DY MON DD HH24:MI:SS YYYY'),'DD-MON-YYYY') 
                and TRUNC(t.date) <= to_char(to_date(?,'DY MON DD HH24:MI:SS YYYY'),'DD-MON-YYYY')
        GROUP BY ROLLUP(Region) ORDER BY Region

Answer 1

If you are missing East region in the players table then you have to get a list of regions from another table and left join with the results of your query. If you do not want to create a table (such as one-time reports) you can construct a table using union and dual, like:

select region.long_name, region.short_name 
from (
    select 'SouthEast' long_name, 'SE' short_name from dual
    union all
    select 'MidWest' long_name, 'MW' short_name from dual
    union all
    select 'SouthWest' long_name, 'SW' short_name from dual
    union all
    select 'East' long_name, 'E' short_name from dual
    union all
    select 'West' long_name, 'W' short_name from dual
) region

When you create the query you select all rows from this region (either dummy or real table) and left join with the sum of players from each region, like:

select 
    region.long_name "Region",
    sum (player.total_players) "Total players"
from (
    select 'SouthEast' long_name, 'SE' short_name from dual
    union all
    select 'MidWest' long_name, 'MW' short_name from dual
    union all
    select 'SouthWest' long_name, 'SW' short_name from dual
    union all
    select 'East' long_name, 'E' short_name from dual
    union all
    select 'West' long_name, 'W' short_name from dual
) region
left join (
    select CASE 
        WHEN STATE IN ('AL','FL','GA','KY','LA','MS','NC','SC','TN') THEN 'SE'
        WHEN STATE IN ('IL','IN','MI','OH','WI') THEN 'MW'
        WHEN STATE IN ('AR','KS','MO','OK','TX') THEN 'SW'
        WHEN STATE IN ('CT') THEN 'E'
        WHEN STATE IN ('CA','NV') THEN 'W'
        ELSE 'Error'
    END region_short_name,
    (players.soccer_players + players.tennis_players) total_players
    from players 
    WHERE TRUNC(t.date) >= to_char(to_date(?,'DY MON DD HH24:MI:SS YYYY'),'DD-MON-YYYY') 
    and TRUNC(t.date) <= to_char(to_date(?,'DY MON DD HH24:MI:SS YYYY'),'DD-MON-YYYY')
) player on player.region_short_name = region.short_name
group by region.long_name

Answer 2

Quick answer:

You commented that the East region definitely has a row in your table, which means that the easy reason it's not being displayed is your WHERE clause. Remove this and the region should re-appear. Only you can tell whether the clause is correct.

---

Following your comment to this answer you need to either have a table of regions and states or you need to create one; it doesn't matter which.

Firstly you need a table unique on region, this is where you store all information at region level

create table regions (
    region varchar2(10)
  , ...
  , constraint pk_regions primary key (region)
    ); 

Next one unique on states, this is where you store all information at state level

create table states (
    state  varchar2(20)
  , ...
  , constraint pk_states primary key (state)
    ); 

You should then populate these two with your information and create a third table that joins between the two.

create table region_states (
    region varchar2(10)
  , state  varchar2(20)
  , constraint pk_region_states primary key (region, state)
  , constraint fk_rs_regions foreign key (region) references regions (region)
  , constraint fx_rs_states foreign key (state) references states (state)
    ); 

Next you should really have a foreign key into the STATES table from your own. This is preferable but may affect your current table so move carefully.

alter table your_table
  add constraint fk_players 
      foreign key (state)
      references states (state)

Lastly, you join to REGION_STATES in order to get the information out.

select rs.region, sum(p.soccer_players + p.tennis_players) as total_players
  from players p
  join region_states rs
    on p.state = rs.state
 group by rs.region

Please also not that I've used SUM() and not COUNT()... you want the total number of players and as the data is already aggregated at state level a COUNT() counts the number of states, not the number of players.

Answer 3

It will give the required output. Could not understand the date in where clause as it is not in the table.

    select CASE 
    WHEN STATE IN ('AL','FL','GA','KY','LA','MS','NC','SC','TN') THEN 'SE'
    WHEN STATE IN ('IL','IN','MI','OH','WI') THEN 'MW'
        WHEN STATE IN ('AR','KS','MO','OK','TX') THEN 'SW'
        WHEN STATE IN ('CT') THEN 'E'
        WHEN STATE IN ('CA','NV') THEN 'W'
        ELSE 'Error'
    END AS Region,
    sum(soccer_players + tennis_players) as total_players,
    from players
    group by region