sql question help

I hope someone have a good idea to get it right:

3 tables

1. user (us)

   column: user_id, state_name

2) web_user (wu)

   column: user_id, illness_id

3) illness (il)

   column: illness_id, illness_name

data:
user table with entries on state_name, CA, Ca, ca, in which row count are CA=2, Ca=3, ca=4

Query:

select count(state_name) from user where upper(state_name) = 'CA';

       get count=9

which is all right.

select distinct(il.illness_name),
count(us.state_name), upper(us.state_name) from web_user wu, illness il, user us
where wu.user_id = us.user_id
and il.illness_id = wu.illness_id
and il.illness_name = 'Cold'
and upper(us.state_name) = 'CA'
group by il.illness_name, us.state_name;

       get 3 rows:
       cold    2    CA
       cold    3    CA
       cold    4    CA

how to get sum of count with all
upper(state_name)='CA' become the answer of  "cold 9 CA"?

Thanks! Received on Fri Mar 10 2006 - 19:02:29 CST