It may be easy to show the result by chart, like this.
Code:
------------------------------ Commands Entered ------------------------------
WITH
source_data(dx , dy) AS (
SELECT edlevel
, COUNT(*)
FROM employee
GROUP BY
edlevel
)
SELECT dx AS "edlevel(x)"
, dy AS "nbr of emp(y)"
, LEFT('====================' , dy)
AS "----+----10---+----20"
FROM source_data
;
------------------------------------------------------------------------------
edlevel(x) nbr of emp(y) ----+----10---+----20
---------- ------------- ---------------------
12 2 ==
14 5 =====
15 2 ==
16 12 ============
17 5 =====
18 4 ====
19 1 =
20 1 =
8 record(s) selected.
You can find other examples in Graeme Birchall's "DB2 SQL Cookbook" under the title "Making Charts Using SQL".
DB2 SQL Cookbook
Here are another charts(Vertical graph).
Example (1a):
Code:
------------------------------ Commands Entered ------------------------------
WITH
source_data(dx , dy) AS (
SELECT edlevel
, COUNT(*)
FROM employee
GROUP BY
edlevel
)
, vertical_axis(y) AS (
VALUES -1
UNION ALL
SELECT y + 1
FROM vertical_axis
WHERE y < (SELECT MAX(dy) + 5
FROM source_data)
AND y < 100000
)
SELECT
CASE
WHEN y = -1 THEN
'edlevel | 5 10 15 20 25'
WHEN y = 0 THEN
'--------+--------*---------*---------*---------*---------*'
WHEN MOD(y , 5) = 0 THEN
LPAD( VARCHAR(y) || ' -' , 9 )
ELSE ' |'
END
||
XMLCAST(
XMLGROUP(
COALESCE( LPAD('| ' , (dx - lag_dx) * 2) , '' ) AS xx
ORDER BY dx
)
AS VARCHAR(60)
)
AS "Vertical bar graph (x:edlevel, y:count of employee)"
FROM
(SELECT dx , y
, LAG(dx , 1 , 0)
OVER(PARTITION BY y
ORDER BY dx) lag_dx
FROM vertical_axis AS v
LEFT OUTER JOIN
source_data AS s
ON v.y BETWEEN 1 AND s.dy
)
GROUP BY
y
ORDER BY
y DESC
;
------------------------------------------------------------------------------
Vertical bar graph (x:edlevel, y:count of employee)
----------------------------------------------------------------------------------------------------------------------
|
|
15 -
|
|
| |
| |
10 - |
| |
| |
| |
| |
5 - | | |
| | | | |
| | | | |
| | | | | | |
| | | | | | | | |
--------+--------*---------*---------*---------*---------*
edlevel | 5 10 15 20 25
19 record(s) selected.
If horizontal axis(x) is not numeric...
Example (2):
Code:
------------------------------ Commands Entered ------------------------------
WITH
source_data(dx , dy) AS (
SELECT workdept
, DEC( AVG(salary) , 9 , 0 )
FROM employee
GROUP BY
workdept
)
, vertical_axis(y) AS (
VALUES -2
UNION ALL
SELECT y + 1
FROM vertical_axis
WHERE y < (SELECT MAX(dy) / 5000 + 3
FROM source_data)
AND y < 100000
)
SELECT CASE
WHEN y <= 0 THEN
SUBSTR( 'avg. salary|department |-----------+' , 25 + y * 12 , 12 )
WHEN MOD(y , 5) = 0 THEN
LPAD( VARCHAR(y * 5000) || ' -' , 12 )
ELSE ' |'
END
||
XMLCAST(
XMLGROUP(
CASE y
WHEN -2 THEN
LPAD( VARCHAR(INT(dy)) || '|' , 6 )
WHEN -1 THEN
' ' || dx || ' |'
WHEN 0 THEN
'-----+'
ELSE
COALESCE( LPAD( '| ' , (x - lag_x) * 6 ) , '' )
END AS xx
ORDER BY x )
AS VARCHAR(60)
)
AS "Vertical bar graph (x:department, y:average salary)"
FROM
(SELECT dx , dy , x , y
, LAG(x , 1 , 0)
OVER(PARTITION BY y
ORDER BY x) lag_x
FROM vertical_axis
LEFT OUTER JOIN
(SELECT dx , dy
, ROW_NUMBER() OVER(ORDER BY dx) AS x
FROM source_data
)
ON y < (dy + 2500) / 5000
)
GROUP BY
y
ORDER BY
y DESC
;
------------------------------------------------------------------------------
Vertical bar graph (x:department, y:average salary)
------------------------------------------------------------------------
|
|
100000 -
| |
| | |
| | |
| | | | |
75000 - | | | |
| | | | |
| | | | |
| | | | |
| | | | | | | |
50000 - | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
25000 - | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
-----------+-----+-----+-----+-----+-----+-----+-----+-----+
department | A00 | B01 | C01 | D11 | D21 | E01 | E11 | E21 |
avg. salary|89500|94250|80156|56900|53486|80175|46998|53827|
25 record(s) selected.
dBforums
>
Fun with SQL(Vertical graph)
01-15-11, 10:49
Linear Mode
