Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Group By Clause Help

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Group By Clause Help

    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

  3. #3
    Join Date
    Sep 2003
    Posts
    14

    Re: Group By Clause Help

    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Group By Clause 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).

  5. #5
    Join Date
    Sep 2003
    Posts
    14
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •