a sql to do a statistics report on some records

[searain]

For example, I have this table the value for the fields of Chicago and New
York are 1 or 0.

TABLE1

CODE CHICAOG NEW YORK

1001 1 0
1003 0 1
1005 1 1
1001 1 1
1005 1 0

I want to have a satistics report generated from the values of table 1. I
want to count how many 1 of CHICAGO a CODE has, and how many 1 of NEW YORK a
CODE has. And how many 1 (CHICAOG & NEW YORK) a CODE has.

It should generate the report like this.

STATISTICS REPORT FROM TABLE 1

CODE CHICAGO NEW YORK TOTAL

1001 2 1 3
1003 0 1 1
1005 2 1 3

Thanks!

Hui

[tomhath]

I didn't test this, but if you look at it you should be able to get it working:

select CODE, 
  sum (CHICAGO) as CHICAGO,
  sum (NEWYORK) as NEWYORK,
  sum (case when CHICAGO = 1 and NEWYORK = 1 then 1 else 0 end)
from TABLE1
group by CODE
ORDER BY CODE