Summarize and CrossTab Possibilities

[sxschech]

I've started learning SQL*Plus using one of those 1 day class books and would like to know how I might enhance this report. The example in the book didn't show for multiple conditions, so perhaps I have to write the query another way? I would like to insert a break between the F and T and be able to put in different description for those subtotals such as 'Total F' and 'Total T'. Also how would I remove the second overall total. I have changed the numbers since this is taken from actual data.

[tt]
Term L Co APPLIED ACCEPTED NETACCEPTED DENIED CANCELLED WITHDRAWN DEPOSITED
------ - -- ---------- ---------- ----------- ---------- ---------- ---------- ----------
200740 F LA 1111 1111 1111 1111 1111 0 1111
200740 F SC 2222 222 222 222 222 0 22
200740 F BN 3333 333 333 333 333 0 33
200740 F NS 444 444 444 444 444 0 44
200740 F 5555 5555 5555 5555 5555 0 555
200740 T LA 1111 1111 1111 1111 1111 0 1111
200740 T SC 2222 222 222 222 222 0 22
200740 T BN 3333 333 333 333 333 0 33
200740 T NS 444 444 444 444 444 0 44
200740 T 5555 5555 5555 5555 5555 0 555
200740 5555 5555 5555 5555 5555 0 555[/tt]

So that it looks more like this:
[tt]
Term L Co APPLIED ACCEPTED NETACCEPTED DENIED CANCELLED WITHDRAWN DEPOSITED
------ - -- ---------- ---------- ----------- ---------- ---------- ---------- ----------
200740 F LA 1111 1111 1111 1111 1111 0 1111
200740 F SC 2222 222 222 222 222 0 22
200740 F BN 3333 333 333 333 333 0 33
200740 F NS 444 444 444 444 444 0 44
200740 Total F 5555 5555 5555 5555 5555 0 555

200740 T LA 1111 1111 1111 1111 1111 0 1111
200740 T SC 2222 222 222 222 222 0 22
200740 T BN 3333 333 333 333 333 0 33
200740 T NS 444 444 444 444 444 0 44
200740 Total T 5555 5555 5555 5555 5555 0 555

200740 Total 6666 6666 6666 6666 6666 0 666
[/tt]

This is what I currently have:

SQL> SET PAGESIZE 66
SQL> SET LINESIZE 100
SQL> COLUMN ACADEMIC_PERIOD    HEADING "Term"  FORMAT A6
SQL> COLUMN Student_Population HEADING "Level" FORMAT A1
SQL> COLUMN College            HEADING "College" FORMAT A2
SQL> SELECT ACADEMIC_PERIOD, Student_Population, 
  2  College,  COUNT(ID) Applied,
  3  SUM(CASE WHEN LATEST_DECISION IN ('CC', 'CD', 'CP', 'CX', 'DC', 'HN', 'N6', 'N7', 'N8', 'PB', '
PH', 'PT', 'QA', 'TL', 'TO', 'US', 'WA', 'WC', 'WT') THEN 1 ELSE 0 END) Accepted, 
  4  SUM(CASE WHEN LATEST_DECISION = 'CC' OR (inst_admit_any_time_ind = 'Y' and LATEST_DECISION <> '
CD') THEN 1 ELSE 0 END) NetAccepted, 
  5  SUM(CASE WHEN LATEST_DECISION = 'RJ' THEN 1 ELSE 0 END) Denied, 
  6  SUM(CASE WHEN LATEST_DECISION IN ('CA','CX') THEN 1 ELSE 0 END) Cancelled, 
  7  SUM(CASE WHEN LATEST_DECISION IN ('WA') THEN 1 ELSE 0 END) Withdrawn, 
  8  SUM(CASE WHEN LATEST_DECISION IN ('PB','PH','PT','WA') THEN 1 ELSE 0 END) Deposited, 
  9  SUM(CASE WHEN LATEST_DECISION IN ('PB','PH','PT','WA') THEN 1 ELSE 0 END) ITE
 10  FROM ODSMGR.ADMISSIONS_APPLICATION 
 11  WHERE ( SUBSTR(PROGRAM,1,2) <> 'ND' ) AND ( PROGRAM IS NOT NULL  ) 
 12  AND ( STUDENT_LEVEL IN ('UG') ) 
 13  AND ( ACADEMIC_PERIOD IN ('200740') )
 14  AND ( STUDENT_POPULATION <> 'S')
 15  GROUP BY ROLLUP(ACADEMIC_PERIOD, Student_Population, College)
 16  ORDER BY 1,2,DECODE (COLLEGE,'LA', 1, 'SC', 2, 'BN', 3, 'NS', 4, 'PS', 5);

The second part of the question is whether this can be cross tabbed so that College goes across and APPLIED, ACCEPTED, etc go down something like this:

[tt]
Term L STATUS LA SC BN NS SC TOTAL
------ - ------------ ---- ---- ----- ------ ----- -----
200740 F APPLIED
200740 F ACCEPTED
200740 F NET ACCEPTED
...
...
Total F
...
...
Total T
...
...
Total[/tt]

 

[DeepDiverMom]

sxschech,

First, let me ask about the intermediate and final totals: Those rows seem to be in your data already rather than calculated...is that correct? If so, do you want the query to do the correct sub- and final-totaling calculations without the bogus sub and final total rows?

 

[LKBrwnDBA]

Before the SELECT add this:

...etc...
COMP SUM OF APPLIED   	ON Term
COMP SUM OF ACCEPTED 	ON Term
COMP SUM OF NETACCEPTED ON Term    
COMP SUM OF DENIED  	ON Term
COMP SUM OF CANCELLED  	ON Term
COMP SUM OF WITHDRAWN  	ON Term
COMP SUM OF DEPOSITED	ON Term
COMP SUM OF APPLIED   	ON REPORT
COMP SUM OF ACCEPTED 	ON REPORT
COMP SUM OF NETACCEPTED ON REPORT    
COMP SUM OF DENIED  	ON REPORT
COMP SUM OF CANCELLED  	ON REPORT
COMP SUM OF WITHDRAWN  	ON REPORT
COMP SUM OF DEPOSITED	ON REPORT
BREAK ON Term ON REPORT
SELECT ...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

sxschech seems to have it correct, it seems the totals are already in the data.

Then you would need to use some kind of DECODE or CASE to identify these lines.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sxschech]

The totals are created by the individual sum function and by the Group by Rollup function.

 

[sxschech]

Thanks LKBrwnDBA, once I removed the Rollup and then your example worked.

Still trying to figure out how to position the labels other than the default location and also how the query might be adjusted/rewritten to display so STATUS goes down and School goes across.

 

[sxschech]

I figured out how to write the SQL so that the information is "flipped". This used 10 Union queries to do it. This report is further broken down by gender, so doing it the present way adds two additional queries (for a total of three) for each union query. Is there an alternate method of producing the output that may not need so many queries?


Also, if the data cols are numeric, can I align or format the headers other than right? From this:
[tt]
LA SC BN NS
Term Level STATUS M F T M F T M F T M F T
------ ----- -------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
200740 F Applied 1111 1111 1111 111 111 1111 111 111 1111 11 111 111
200740 Accepted 222 222 222 222 222 222 222 222 222 22 222 222
[/tt]

To This?
[tt]
LA SC BN NS
Term Level STATUS M F T M F T M F T M F T
------ ----- -------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
200740 F Applied 1111 1111 1111 111 111 1111 111 111 1111 11 111 111
200740 Accepted 222 222 222 222 222 222 222 222 222 22 222 222
[/tt]