Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: Percent Calculation

    To Generate a pie chart showing the percentage of customers by region, from the following tables:
    Client (Client_id, Region_Cd, ...)
    Region (Region_Cd, Region_Nm, ...) where Region_Cd is the region code and Region_Nm is the region name ,

    the next result is required:
    Region_Nm, Percent (1 row for each region)

    Thanks in advance,
    ALF.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example:
    (If you couldn't use OLAP specification, please show your DB2 version/release and platform(OS).)

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Client (Client_id, Region_Cd, value) AS (
    VALUES
      (101 , 1 , 'A')
    , (102 , 1 , 'B')
    , (103 , 1 , 'C')
    , (104 , 2 , 'D')
    , (105 , 2 , 'E')
    , (107 , 2 , 'F')
    , (109 , 2 , 'G')
    , (110 , 2 , 'H')
    , (112 , 3 , 'I')
    , (113 , 5 , 'J')
    , (118 , 5 , 'K')
    , (126 , 5 , 'L')
    , (197 , 5 , 'M')
    )
    , Region (Region_Cd, Region_Nm) AS (
    VALUES
      (1 , 'Region one')
    , (2 , 'Region two')
    , (3 , 'Region three')
    , (4 , 'Region four')
    , (5 , 'Region five')
    )
    SELECT MAX(Region_Nm) AS Region_Nm
         , COUNT(Client_id) * 100.
           / SUM( COUNT(Client_id) ) OVER()  AS Percent
     FROM
           Region R
     LEFT OUTER JOIN
           Client C
       ON  c.Region_Cd = r.Region_Cd
     GROUP BY
           r.Region_Cd
     ORDER BY
           r.Region_Cd;
    ------------------------------------------------------------------------------
    
    REGION_NM    PERCENT                          
    ------------ ---------------------------------
    Region one                23.07692307692307692
    Region two                38.46153846153846153
    Region three               7.69230769230769230
    Region four                0.00000000000000000
    Region five               30.76923076923076923
    
      5 record(s) selected.
    Last edited by tonkuma; 10-13-10 at 09:15. Reason: Change formatting.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure you want a db2 solution?

    --> http://www.dbforums.com/microsoft-sq...er-region.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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