Adding a Custom Aggregate Value In Chart

yrraja

Hi All

I have data of the form:

Action - Number - Date

The value of "Action" is Add or Delete, "Number" is the total number of actions in the given "Date".

I want to create a bar chart that shows following 3 bars:

<Total number of "Adds"> <Total number of "Deletes"> <Net Change (Total Adds - Total Deletes)>

I can easily create the chart with the first two columns but i have not been able to figure out how to show the third column. Can someone guide how to active this?

Thanks

Hans_vd

Hi Yrraja,<br />
<br />
I recreated your situation:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>CREATE TABLE your_table (
action_col VARCHAR2(20),
number_col NUMBER,
date_col DATE
);

INSERT INTO your_table VALUES ('ADD', 15, to_date('21-03-2012', 'dd-mm-yyyy'));
INSERT INTO your_table VALUES ('DELETE', 12, to_date('21-03-2012', 'dd-mm-yyyy'));
INSERT INTO your_table VALUES ('ADD', 29, to_date('22-03-2012', 'dd-mm-yyyy'));
INSERT INTO your_table VALUES ('DELETE', 7, to_date('22-03-2012', 'dd-mm-yyyy'));

COMMIT;</pre>
<br />
Then I wrote this query:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT action_col,
number_col,
date_col
FROM your_table
UNION ALL
SELECT 'NET CHANGE' AS action_col,
SUM (CASE WHEN action_col = 'ADD' THEN number_col ELSE 0 END) - SUM (CASE WHEN action_col = 'DELETE' THEN number_col ELSE 0 END) AS number_col,
date_col
FROM your_table
GROUP BY date_col
ORDER BY date_col, action_col;</pre>
<br />
That has these results:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
ADD 15 21-3-2012
DELETE 12 21-3-2012
NET CHANGE 3 21-3-2012
ADD 29 22-3-2012
DELETE 7 22-3-2012
NET CHANGE 22 22-3-2012
</pre>
<br />
So now you have three different values for your action_col, exactly what your chart needs.<br />
<br />
I recreated your problem in Oracle, can you translate it to your environment?<br />
Hope it helps.<br />
<br />
Regards<br />
Hans