DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Reporting Query Help

  1. #1
    Join Date
    May 2002
    Posts
    29

    Question

    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.


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    select v.zip_code "ZIP CODE",
    sum(decode(v.property_type_desc, 'Private', 1, 0)) Private,
    sum(decode(v.property_type_desc, 'Public', 1, 0)) Public
    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
    group by v.zip_code;

  3. #3
    Join Date
    May 2002
    Posts
    29

    Talking

    Ooh, right on the money! I knew I had to use the sum function but didn't know with what combination. Thanks Shestakov!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width