Here is the scenarios and wondering if anyone can help:
Here is a sample SQL statement:
select v.zip_code "ZIP CODE", v.property_type_desc
from violation_property v,
incident i,
sector_zip_code s
where
i.property_key = v.property_key
and i.reported_date between TO_DATE ('10-Jan-1111') and TO_DATE ('7-Apr-2002')
and v.zip_code = s.zip_code
order by v.zip_code;
Here is a sample result from the query:
ZIP CODE PROPERT
---------- -------
98104 Private
98106 Private
98106 Private
98108 Public
98108 Public
98108 Private
98108 Private
98108 Private
98134 Public
If the v.property_type_desc column contains only either value, private or public, how can I break the above query to present two extra columns in place of the v.property_type_desc. Here is what I mean (the format I am looking for):
ZIP CODE PRIVATE PUBLIC
------------ ----------- ---------
98104 1 0
98106 2 0
98108 3 2
98134 0 1
So I want to group by the zip code and then count the either the total number of private or public within the v.property_type_desc column for each distinct zip code. Then display a result similar to the format above.