Percent Calculation

arlf · #1 (**permalink**) 10-12-10, 21:55

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.

tonkuma · #2 (**permalink**) 10-13-10, 08:07

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.

r937 · #3 (**permalink**) 10-13-10, 09:25

are you sure you want a db2 solution?

--> Percentaje of Clients Number by Region

Internet Services

Web Development

Digital Marketing

Consumer Tech