Modifying SELECT Query to Create Stacked Bar Chart
I have a request for a placement application tracking system that shows the number of students placed and not placed on the same study program. I am struggling to create an APEX Stacked Bar Chart even if the query returns the results I want.
Query:
SELECT programme_name,
SUM(CASE WHEN (cv_approval_date IS NOT NULL AND application_status_id <> 7) OR
application_status_id IS NULL
THEN 1 ELSE 0 END) as Unplaced,
SUM(CASE WHEN (cv_approval_date IS NOT NULL AND application_status_id <> 7) OR
application_status_id IS NULL
THEN 0 ELSE 1 END) as Placed
FROM programme LEFT JOIN
student USING (programme_id) LEFT JOIN
application USING (student_id)
GROUP BY programme_name;
Output:
PROGRAMME_NAME | PLACED | UNPLACED
BSc (Hons) Computer Science | 2 | 2
BSc (Hons) Computing and Games Development | 1 | 0
BSc (Hons) Web Applications Development | 0 | 1
BSc (Hons) Marine Biology and Coastal Ecology | 1 | 0
The graph is supposed to be similar to this one: the x-axis is the program, the y-axis is the number of students placed and not placed:
http://ruepprich.files.wordpress.com/2011/03/stacked_bar.png?w=550&h=386
How can i do this? Any help would be greatly appreciated!
source to share
When creating a chart in Apex, you can click Sample Chart Query for some example queries that will work with this type of chart.
For a stacked histogram, the following example is given:
SELECT NULL LINK,
ENAME LABEL,
SAL "Salary",
COMM "Commission"
FROM EMP
ORDER BY ENAME
In your case, I think you want your request to represent the following format:
SELECT NULL LINK,
programme_name AS LABEL,
SUM(...) AS "Unplaced",
SUM(...) AS "Placed"
FROM ...
source to share