SQL & PL/SQL

I currently have an issue writing a pl\sql report, I can get part of the way to the results I want but the group by clause is causing problems, because I have to add more columns to the group by, dispersing the figures further, I have tried it with coalesce for each of the task types but I still get the same results, I am getting close to the results I need but not quite there yet. I would really appreciate it if someone could take at look at this for me as it is an urgent requirement.

The report is based on the tables similar to the following:

TASKS, ORGANISATIONS, POSITIONS

A position is a member of an organisation.
A task has a position assigned to it.

The SQL for the tables and to insert the data that would produce the report is detailed below:

CREATE TABLE TASKS
( TASK_ID NUMBER NOT NULL ENABLE,
TASK_TYPE VARCHAR2 (15 BYTE) NOT NULL ENABLE,
STATUS VARCHAR2 (15 BYTE) NOT NULL ENABLE,
POS_ID NUMBER NOT NULL ENABLE,
CONSTRAINT TASKS_PK PRIMARY KEY (TASK_ID));

CREATE TABLE ORGANISATIONS
( ORG_ID NUMBER NOT NULL ENABLE,
ORG_NAME VARCHAR2 (15 BYTE) NOT NULL ENABLE,
CONSTRAINT ORGANISATIONS_PK PRIMARY KEY (ORG_ID));

CREATE TABLE POSITIONS
( POS_ID NUMBER NOT NULL ENABLE,
POS_NAME VARCHAR2 (25 BYTE) NOT NULL ENABLE,
ORG_ID NUMBER NOT NULL ENABLE,
CONSTRAINT POSITIONS_PK PRIMARY KEY (POS_ID));

INSERT INTO ORGANISATIONS (ORG_ID, ORG_NAME) VALUES (1,'ABC');
INSERT INTO ORGANISATIONS (ORG_ID, ORG_NAME) VALUES (2,'DEF');
INSERT INTO ORGANISATIONS (ORG_ID, ORG_NAME) VALUES (3,'EFG');

INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (1,'ABC-1', 1);
INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (3,'ABC-2', 1);
INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (2,'ABC-3', 1);
INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (5,'DEF-2', 2);
INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (4,'DEF-1', 2);
INSERT INTO POSITIONS (POS_ID, POS_NAME, ORG_ID) VALUES (7,'EFG-1', 3);

INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (12,'TASK_TYPE_3','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (11,'TASK_TYPE_2','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (10,'TASK_TYPE_2','LIVE',2);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (9,'TASK_TYPE_2','LIVE',2);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (8,'TASK_TYPE_1','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (7,'TASK_TYPE_1','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (6,'TASK_TYPE_1','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (5,'TASK_TYPE_1','LIVE',3);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (4,'TASK_TYPE_1','LIVE',2);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (3,'TASK_TYPE_3','LIVE',1);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (2,'TASK_TYPE_1','LIVE',1);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (1,'TASK_TYPE_1','LIVE',1);
INSERT INTO TASKS (TASK_ID, TASK_TYPE, STATUS, POS_ID) VALUES (13,'TASK_TYPE_3','LIVE',3);

The report should detail the following information based on the information in the tables:
1st Column
Organisation
ABC
DEF
EFG

2nd Column
No. of Positions in Organsiation
3
2
1
With total of the number of people in all of the organisation 6

3rd Column
Number of tasks assigned to the organisation of task type1
2
1
4

4th Column
Number of tasks assigned to the organisation of task type 2
0
2
1

5th Column
Number of tasks assigned to the organisation of task type 3
1
0
2

Total no of tasks assigned to the Organisation
3
3
7



Message was edited by:
Suzy_r_82

Message was edited by:
Suzy_r_82