1. Registered User
Join Date
May 2006
Posts
16

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)

ALF.

2. Registered User
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 08:15. Reason: Change formatting.

3. SQL Consultant
Join Date
Apr 2002
Location