COLUMN ROOMNUM HEADING ROOMNUM FORMAT 999999999 COLUMN REGDA

COLUMN ROOMNUM HEADING ROOMNUM FORMAT 999999999
COLUMN REGDATE HEADING ARRIVAL DATE FORMAT DATE
COLUMN LASTNAME HEADING CUSTOMER NAME FORMAT A16
COLUMN ADULTCNT HEADING ADULTCNT FORMAT 99999
COLUMN CHILDCNT HEADING CHILDCNT FORMAT 9999

BREAK ON ROOMRATE skip 1 on Roomnum

COMPUTE SUM LABEL subtotal OF ROOMRATE ON ROOMNUM

COMPUTE SUM LABEL \'Grand Total\' OF ROOMRATE ON ROOMNUM

SELECT SUM(ROOMRATE*COUNT(ROOMNUM)) AS ROOMNUM, REGDATE

2 FROM DDI.LEDGER_VIEW BETWEEN <\'01-JUN-15\' AND \'07-JUN-15\'>

3 ORDER BY ROOMNUM, REGDATE;

What is wrong with my code?

The question is ----

Project 2 is about printing a formatted list of room registrations from DDI.LEDGER_VIEW

SPOOL your output to c:\\cs422a\\project2spool.txt

Format COLUMN Headings and Formats for ROOMNUM, REGDATE, LASTNAME, ADULTCNT, CHILDCNT, and ROOMRATE

BREAK on ROOMNUM and COMPUTE a total of ROOMRATE for each ROOMNUM

SELECT the fields indicated above from DDI.LEDGER_VIEW for registrations between \'01-JUN-15\' and \'07-JUN-15\'
(don\'t forget to order by ROOMNUM and REGDATE)

Solution

Instead of using ORDER BY use GROUP BY REGDATE and no need of ROOMNUM in GROUP BY statement.