Results 1 to 3 of 3

Thread: Report help!

  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Unanswered: Report help!

    This'll be the last question from me this week! Thanks for all the help!

    I've created a report, but am unsure on how to do the last bit. Basically, I need to work out the total for each group.

    So total for group 20, 21, 22, 23

    The complete working report without total is

    Code:
    SET PAGESIZE 55
    SET NEWPAGE 0
    SET LINESIZE 140
    
    TTITLE 'Group Bill'
    BTITLE 'Company Confidential' 
    
    COLUMN groups.group_id HEADING 'Group ID'
    COLUMN groups.number_in_group HEADING 'Num in group'
    COLUMN bookings.start_date HEADING 'Starts'
    COLUMN bookings.end_date HEADING 'Ends'
    COLUMN accommodation.chalet_id HEADING 'Chalet ID'
    COLUMN accommodation.number_sleeps HEADING 'Sleeps'
    COLUMN accommodation.number_group_members HEADING 'People in chalet'
    COLUMN accommodation.price HEADING 'Chalet Price'
    COLUMN price_p_person HEADING 'Price Per Person'
    COLUMN total HEADING 'Total Price'
    
    SELECT groups.group_id, groups.number_in_group, bookings.start_date, bookings.end_date, accommodation.chalet_id, accommodation.number_sleeps, accommodation.number_group_members, accommodation.price, accommodation.price/accommodation.number_group_members Price_P_Person, 
    FROM groups, bookings, accommodation
    WHERE groups.group_id = accommodation.group_id
    AND bookings.booking_id = groups.booking_id
    ORDER BY groups.group_id
    
    SPOOL c:/Users/Luke/Desktop/report
    /
    SPOOL OFF
    CLEAR BREAK
    CLEAR COMPUTE
    TTITLE OFF
    BTITLE OFF
    which outputs

    Code:
      GROUP_ID NUMBER_IN_GROUP START_DAT END_DATE   CHALET_ID NUMBER_SLEEPS NUMBER_GROUP_MEMBERS      PRICE Price Per Person                    
    ---------- --------------- --------- --------- ---------- ------------- -------------------- ---------- ----------------                    
            20              15 01-APR-11 14-APR-11          1            15                   15         50       3.33333333                    
            21              14 12-APR-11 19-APR-11          2            10                   10         50                5                    
            21              14 12-APR-11 19-APR-11          3             5                    4         50             12.5                    
            22               7 21-APR-11 28-APR-11          4             7                    7         50       7.14285714                    
            23              11 01-MAY-11 07-MAY-11          5             4                    4         50             12.5                    
            23              11 01-MAY-11 07-MAY-11          6             4                    3         50       16.6666667                    
            23              11 01-MAY-11 07-MAY-11          7             4                    4         50             12.5
    Does anyone know how I can do this. Basically I need to add the PRICE together for each group_id, but as you can see, there maybe 2 or 3 lines for each group_id...

    Any ideas?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Basically I need to add the PRICE together for each group_id
    I see what it says, but I don't understand what you expect as desired results.
    It would help if you posted DDL for table & DML (INSERT) for test data.
    Code:
    SQL> select deptno, sum(sal) from emp group by deptno order by 1;
    
        DEPTNO   SUM(SAL)
    ---------- ----------
    	10	 8750
    	20	18650
    	30	 9400
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    as seperate commands before the select

    break on group_id

    compute sum of price_p_person on group_id
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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