Reporting Query Help
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