Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?

- thanks for your time.

SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;

DONOR TYPE PROGRAM CONTRIBUTION QTR2
-------------------- ------------------------- -----------------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50

What prevents you from doing this?:

SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;

DONOR TYPE CONTRIBUTION QTR2
-------------------- -----------------
Corporate Donors 250
Foundations 225
Individuals 50

How can I get tthe output to look like this?

DONOR TYPE PROGRAM CONTRIBUTION QTR2
-------------------- ------------------------- -----------------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50

Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50


- thanks for your help

Oh I see, you mean suppress the output of the repeated value?

In SQL Plus, use:

SQL> BREAK ON "DONOR TYPE"

Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).

Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?

SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;

MEMBER TARGET CONT. QTR1 % OF PROJECTION
--------------- ---------- ---------- ---------------
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5