Forum Stats

  • 3,855,294 Users
  • 2,264,497 Discussions
  • 7,905,965 Comments

Discussions

Show counts and averages for 3 categories side by side

708388
708388 Member Posts: 85
edited Jul 13, 2009 11:19PM in SQL & PL/SQL
The following query was originally developed for calculating GPAs (thread id 920965). I have modified it to perform the same kind of summary on Scores.
The issue is that there are 3 types of scores as opposed to one type of GPA. When I run the query for one score type, the results for
that score type come out fine, however, when I tried adding in the additional score types (they have been commented out in the query below), the results
don't come out right when more than one score was in the query. Can this query be modified to handle multiple score types or will I
need separate runs for each type?

Here is the output as it should look. The numbers in the col are counts for the number of students who have a score within each range
and the overall average score for each category is at the bottom.

SAMPLE SUMMARY:
  Score Range	Read	Math	Write
			
 001 - 299	18	12	25
 300 - 349	60	50	50
 350 - 399	235	150	207
 400 - 449	523	400	463
 450 - 499	840	870	857
 500 - 549	1300	1189	1314
 550 - 599	1321	1400	1425
 600 - 649	1298	1280	1262
 650 - 699	605	940	737
 700 - 749	200	265	330
 750 - 800	109	119	102
 NO SCORES	1450	1284	1187
			
 TOTAL	7959	7959	7959
			
 AVERAGE	553	563	559
Sample Detail:
ID	READ	MATH	WRITE
121212	570	520	550
112121	650	570	600
121121	
111221	
111122	600	625	610
The way the query runs now, if I only run it for Read, the Read numbers come out fine, if I only run it for Math, the Math numbers
come out fine, etc. When I typed all three into the query, the numbers came out wrong.


--This part creates the range table. Generally only need to create once
CREATE TABLE    Score_Range
AS
SELECT  000 AS low_score, 300 AS high_score, '001 - 299' AS display_txt, 1  AS range_sort    FROM dual    UNION ALL
SELECT  300 AS low_score, 350 AS high_score, '300 - 349' AS display_txt, 2  AS range_sort    FROM dual    UNION ALL
SELECT  350 AS low_score, 400 AS high_score, '350 - 399' AS display_txt, 3  AS range_sort    FROM dual    UNION ALL
SELECT  400 AS low_score, 450 AS high_score, '400 - 449' AS display_txt, 4  AS range_sort    FROM dual    UNION ALL
SELECT  450 AS low_score, 500 AS high_score, '450 - 499' AS display_txt, 5  AS range_sort    FROM dual    UNION ALL
SELECT  500 AS low_score, 550 AS high_score, '500 - 549' AS display_txt, 6  AS range_sort    FROM dual    UNION ALL
SELECT  550 AS low_score, 600 AS high_score, '550 - 599' AS display_txt, 7  AS range_sort    FROM dual    UNION ALL
SELECT  600 AS low_score, 650 AS high_score, '600 - 649' AS display_txt, 8  AS range_sort    FROM dual    UNION ALL
SELECT  650 AS low_score, 700 AS high_score, '650 - 699' AS display_txt, 9  AS range_sort    FROM dual    UNION ALL
SELECT  700 AS low_score, 750 AS high_score, '700 - 749' AS display_txt, 10 AS range_sort    FROM dual    UNION ALL
SELECT  750 AS low_score, 999 AS high_score, '750 - 800' AS display_txt, 11 AS range_sort    FROM dual    UNION ALL
SELECT  NULL,          NULL,           'No Scores',                      13           FROM dual;
------------------------------------------------------------------------------------------------------------------------

--This part is the actual query to use to see the summary
WITH interesting_score_stat AS
(
    SELECT stu_population, '1Applied' Status, college,
           sat_read, sat_math, sat_write
    FROM   gpa_stat
    WHERE  stu_population  in ('F','T')
    AND    academic_period = '200940'
UNION ALL
    SELECT stu_population, '2Accepted' Status, college,
           sat_read, sat_math, sat_write
    FROM   gpa_stat
    WHERE  stu_population  in ('F','T')
    AND    academic_period = '200940'
    AND    accepted = 1
UNION ALL
    SELECT stu_population, '3Deposit' Status, college,
           sat_read, sat_math, sat_write
    FROM   gpa_stat
    WHERE  stu_population  in ('F','T')
    AND    academic_period = '200940'
    AND    accepted = 1
    AND    deposit  = 1
),       all_colleges      AS
(
    SELECT DISTINCT stu_population, Status, college
    FROM   interesting_score_stat
    
)
SELECT c.stu_population, 
       c.Status,
       c.college,
       r.display_txt           AS scorerange,
       COUNT (s.college)       AS count,
       round(NVL(AVG(s.sat_read),0),0)  AS avgRead
--       round(NVL(AVG(s.sat_math),0),0)  AS avgMath,
--       round(NVL(AVG(s.sat_write),0),0) AS avgWrite
FROM  all_colleges           c
CROSS JOIN score_range         r
LEFT OUTER JOIN interesting_score_stat s    
  ON ( c.stu_population   = s.stu_population
 AND   c.status    = s.status
 AND   c.college   = s.college
 AND   r.low_score  <= s.sat_read
 --AND   r.low_score  <= s.sat_math
 --AND   r.low_score  <= s.sat_write
 AND   r.high_score  > s.sat_read
-- AND   r.high_score  > s.sat_math
-- AND   r.high_score  > s.sat_write
     )
  OR ( c.stu_population  =  s.stu_population 
 AND   c.status   =  s.status
 AND   c.college  =  s.college
 AND   r.low_score     IS NULL
 AND   s.sat_read      IS NULL
-- AND   s.sat_math      IS NULL
-- AND   s.sat_write     IS NULL
     )
GROUP BY c.stu_population, 
         c.status, 
         cube ( c.college, 
                r.display_txt
              )
ORDER BY c.stu_population, 
         c.status, 
         c.college, 
         r.display_txt
 
;
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    Answer ✓
    Hi,

    To make more manageable sample data, I cut socre_range down to four rows:
    SELECT  450 AS low_score, 500 AS high_score, '450 - 499' AS display_txt, 5  AS range_sort    FROM dual    UNION ALL
    SELECT  500 AS low_score, 550 AS high_score, '500 - 549' AS display_txt, 6  AS range_sort    FROM dual    UNION ALL
    SELECT  550 AS low_score, 600 AS high_score, '550 - 599' AS display_txt, 7  AS range_sort    FROM dual    UNION ALL
    SELECT  NULL,          NULL,           'No Scores',                      13           FROM dual;
    and used this data in gpa_stat:
    select '12345678' ID, '200940' Academic_period, 'Freshmen' Stu_Pop, 'F' Stu_population, 'LA' College, 1 Applied, 1 Accepted, 1 Deposit, 560 SAT_READ, 590 SAT_MATH, 510 SAT_WRITE from dual union all
    select '23456789',     '200940',    'Transfer',    'T',     'LA',    1,    1,    0,  null, null, null    from dual union all            
    select '34567890',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    500,    510,    540 from dual union all
    select '45678901',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    530,    520,    630 from dual union all
    select '56789012',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    550,    520,    540 from dual union all
    select '67890123',    '200940',    'Freshmen',    'F',    'LA',    1,    1,    1,    null,    null,  null from dual 
    This query:
    WITH   cntr	   AS
    (
    	SELECT	LEVEL	test_code
    	FROM	dual
    	CONNECT BY	LEVEL <= 3	-- # of measure columns (sat_read, sat_math and sat_write)
    )
    ,	unpivoted_data	    AS
    (
    	SELECT	s.stu_population
    	,	CASE
    			WHEN  accepted = 1
    			AND   deposit  = 1
    				       THEN	3
    			WHEN  accepted = 1
    				       THEN	2
    				       ELSE	1
    		END	AS status_lvl
    	,	s.college
    	,	c.test_code
    	,	CASE	c.test_code
    			WHEN  1		THEN  sat_read
    			WHEN  2		THEN  sat_math
    			WHEN  3		THEN  sat_write
    		END	AS score
    	FROM		gpa_stat	s
    	CROSS JOIN	cntr		c
    	WHERE   stu_population		IN ('F', 'T')	-- Do all filtering here
    	AND	academic_period		= '200940'
    )
    ,	all_colleges	AS
    (
    	SELECT DISTINCT	college
    	FROM   		unpivoted_data
    )
    ,	all_populations	AS
    (	SELECT DISTINCT	stu_population
    	FROM   		unpivoted_data
    )
    SELECT	  p.stu_population
    ,	  a.display_txt						AS status
    ,	  c.college
    ,	  NVL ( r.display_txt
    	      , ' (Total)'
    	      )							AS scorerange
    ,	  COUNT	(CASE WHEN u.test_code = 1 THEN 1     END)	AS read 
    ,	  AVG 	(CASE WHEN u.test_code = 1 THEN score END)	AS avgread
    ,	  COUNT (CASE WHEN u.test_code = 2 THEN 1     END)	AS math
    ,	  AVG 	(CASE WHEN u.test_code = 2 THEN score END)	AS avgmath
    ,	  COUNT (CASE WHEN u.test_code = 3 THEN 1     END)	AS write 
    ,	  AVG 	(CASE WHEN u.test_code = 3 THEN score END)	AS avgwrite
    FROM	      	    all_populations p
    CROSS JOIN	    all_status	    a
    CROSS JOIN	    all_colleges    c
    CROSS JOIN	    score_range     r
    LEFT OUTER JOIN     unpivoted_data  u	ON  	u.stu_population  =  p.stu_population
         	   	    		    	AND	u.status_lvl	  >= a.lvl_id
         	   	    		    	AND 	(	(	  u.score      >=  r.low_score
         	   	    		    	   		AND	  u.score      <   r.high_score
    					   		)
    						OR	(	  u.score      IS NULL
    					   		AND	  r.low_score  IS NULL
    					   		)
    					    	)
    					AND	u.college	  =  c.college
    GROUP BY  p.stu_population
    ,     	  a.display_txt
    ,	  c.college
    ,	  ROLLUP ((r.display_txt))
    ORDER BY  p.stu_population
    ,     	  a.display_txt
    ,         c.college
    ,	  GROUPING (r.display_txt)
    ,     	  MIN (r.range_sort)
    ;
    produces this output:
    STU STATUS    CO SCORERANG READ READ MATH MATH WRITE WRITE
    --- --------- -- --------- ---- ---- ---- ---- ----- -----
    F   1Applied  BN 450 - 499    0         0          0
    F   1Applied  BN 500 - 549    2  515    3  517     2   540
    F   1Applied  BN 550 - 599    1  550    0          0
    F   1Applied  BN No Scores    0         0          0
    F   1Applied  BN  (Total)     3  527    3  517     2   540
    F   1Applied  LA 450 - 499    0         0          0
    F   1Applied  LA 500 - 549    0         0          1   510
    F   1Applied  LA 550 - 599    1  560    1  590     0
    F   1Applied  LA No Scores    1         1          1
    F   1Applied  LA  (Total)     2  560    2  590     2   510
    F   2Accepted BN 450 - 499    0         0          0
    F   2Accepted BN 500 - 549    2  515    3  517     2   540
    F   2Accepted BN 550 - 599    1  550    0          0
    F   2Accepted BN No Scores    0         0          0
    F   2Accepted BN  (Total)     3  527    3  517     2   540
    F   2Accepted LA 450 - 499    0         0          0
    F   2Accepted LA 500 - 549    0         0          1   510
    F   2Accepted LA 550 - 599    1  560    1  590     0
    F   2Accepted LA No Scores    1         1          1
    F   2Accepted LA  (Total)     2  560    2  590     2   510
    F   3Deposit  BN 450 - 499    0         0          0
    F   3Deposit  BN 500 - 549    2  515    3  517     2   540
    F   3Deposit  BN 550 - 599    1  550    0          0
    F   3Deposit  BN No Scores    0         0          0
    F   3Deposit  BN  (Total)     3  527    3  517     2   540
    F   3Deposit  LA 450 - 499    0         0          0
    F   3Deposit  LA 500 - 549    0         0          1   510
    F   3Deposit  LA 550 - 599    1  560    1  590     0
    F   3Deposit  LA No Scores    1         1          1
    F   3Deposit  LA  (Total)     2  560    2  590     2   510
    
                                     AVG       AVG         AVG
    STU STATUS    CO SCORERANG READ READ MATH MATH WRITE WRITE
    --- --------- -- --------- ---- ---- ---- ---- ----- -----
    T   1Applied  BN 450 - 499    0         0          0
    T   1Applied  BN 500 - 549    0         0          0
    T   1Applied  BN 550 - 599    0         0          0
    T   1Applied  BN No Scores    0         0          0
    T   1Applied  BN  (Total)     0         0          0
    T   1Applied  LA 450 - 499    0         0          0
    T   1Applied  LA 500 - 549    0         0          0
    T   1Applied  LA 550 - 599    0         0          0
    T   1Applied  LA No Scores    1         1          1
    T   1Applied  LA  (Total)     1         1          1
    T   2Accepted BN 450 - 499    0         0          0
    T   2Accepted BN 500 - 549    0         0          0
    T   2Accepted BN 550 - 599    0         0          0
    T   2Accepted BN No Scores    0         0          0
    T   2Accepted BN  (Total)     0         0          0
    T   2Accepted LA 450 - 499    0         0          0
    T   2Accepted LA 500 - 549    0         0          0
    T   2Accepted LA 550 - 599    0         0          0
    T   2Accepted LA No Scores    1         1          1
    T   2Accepted LA  (Total)     1         1          1
    T   3Deposit  BN 450 - 499    0         0          0
    T   3Deposit  BN 500 - 549    0         0          0
    T   3Deposit  BN 550 - 599    0         0          0
    T   3Deposit  BN No Scores    0         0          0
    T   3Deposit  BN  (Total)     0         0          0
    T   3Deposit  LA 450 - 499    0         0          0
    T   3Deposit  LA 500 - 549    0         0          0
    T   3Deposit  LA 550 - 599    0         0          0
    T   3Deposit  LA No Scores    0         0          0
    T   3Deposit  LA  (Total)     0         0          0
    
    60 rows selected.
    Among the assumptions I made were some about status.
    The different status values of status seem to be subsets of one another. That is, any row that is qualifies as '3Deposit' is also counted as '2Accepted', and any row that is counted as '2Accepted' is also counted as '1Applied'.
    For the same reason that you should have a scre_range table, you should also have a table for theses statuses, like this:
    CREATE TABLE  all_status
    AS
    SELECT	1 as lvl_id, '1Applied' AS display_txt	FROM dual	UNION ALL
    SELECT	2,   	     '2Accepted'   		FROM dual	UNION ALL
    SELECT	3,	     '3Deposit'			FROM dual;
    where the higher lvl_ids are assumed to include the lower ones (for example, lvl_id 1 is a subset of 2 and 3).

    When there are no rows in a group, the average columns in the output above are left NULL.
    If you want 0's in those places, use "NVL (AVG (...), 0)" instead of "AVG (...)".
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 6, 2009 6:40PM
    Hi,

    It looks like you want to consider each row of gpa_stat as three separate rows: one with the sat_read value, one with sat_math, and one with sat_write.
    if that's so, you should first un-pivot your data, by cross-joining it to a table with three rows.

    The sample results you posted seem to have one row per grade_range, for all colleges and statuses, and the numbers don't seem to have anything to do with the sample data you posted. Do you actually want to get separate figures for each college and status, then totals for all colleges (but each status separately), and each status (with each college separately)?
    Post some sample data (from the gpa_stat table) that shows what you're really trying to do, and the results you want from that data. Post executable INSERT statements (or CREATE TABLE AS, like you did for score_range).

    I think yiou may wind up doing something like this:
    WITH   cntr	   AS
    (
    	SELECT	LEVEL	AS test_code
    	FROM	dual
    	CONNECT BY	LEVEL <= 3	-- # of measure columns (sat_read, sat_math and sat_write)
    )
    ,	unpivoted_data	    AS
    (
    	SELECT	s.college
    	,	s.stu_population
    	,	CASE
    			WHEN  accepted = 1
    			AND   deposit  = 1
    				       THEN	'3 Deposit'
    			WHEN  accepted = 1
    				       THEN	'2 Accepted'
    				       ELSE	'1 Applied'
    		END	AS status
    --	,	...				-- plus anything from s that you need later on	
    	,	c.test_code
    	,	CASE	c.test_code
    			WHEN  1		THEN  sat_read
    			WHEN  2		THEN  sat_math
    			WHEN  3		THEN  sat_write
    		END	AS score
    	FROM		gpa_stat	s
    	CROSS JOIN	cntr		c
    	WHERE   stu_population		IN ('F', 'T')	-- Do all filtering here
    	AND	academic_period		= '200940'
    )
    ,	all_colleges	AS
    (
    	SELECT DISTINCT	college
    	FROM   		unpivoted_data
    )
    SELECT	  r.display_txt
    ,	  COUNT			  (CASE WHEN u.test_code = 1 THEN 1     END)	AS read 
    ,	  CASE
    		WHEN  GROUPING (display_txt) = 1
    		THEN  ROUND ( AVG (CASE WHEN u.test_code = 1 THEN score END)
    		      	    , 2
    			    )
    	  END									AS read_avg
    ,	  COUNT 		  (CASE WHEN u.test_code = 2 THEN 1     END)	AS math
    ,	  CASE
    		WHEN  GROUPING (display_txt) = 1
    		THEN  ROUND ( AVG (CASE WHEN u.test_code = 2 THEN score END)
    		      	    , 2
    			    )
    	  END									AS math_avg
    ,	  COUNT 		  (CASE WHEN u.test_code = 3 THEN 1     END)	AS write 
    ,	  CASE
    		WHEN  GROUPING (display_txt) = 1
    		THEN  ROUND ( AVG (CASE WHEN u.test_code = 3 THEN score END)
    		      	    , 2
    			    )
    	  END									AS write_avg
    FROM	      	    score_range     r
    LEFT OUTER JOIN     unpivoted_data  u	ON (	u.score	>=  r.low_score
         	   	    		    	   AND	u.score <   r.high_score
    					   )
    					OR (	u.score		 IS NULL
    					   AND	r.low_score	 IS NULL
    					   )
    GROUP BY  CUBE ( r.display_txt
    	       )
    ORDER BY  GROUPING (display_txt)
    ,     	  MIN (r.range_sort)
    ;
    With the sample data you posted, this produces:
    DISPLAY_T       READ   READ_AVG       MATH   MATH_AVG      WRITE  WRITE_AVG
    --------- ---------- ---------- ---------- ---------- ---------- ----------
    450 - 499          0                     0                     0
    500 - 549          0                     1                     0
    550 - 599          1                     1                     1
    600 - 649          1                     1                     2
    650 - 699          1                     0                     0
    No Scores          2                     2                     2
                       5     606.67          5     571.67          5     586.67
    I deleted most unused rows from score_range, for more manageable testing. You could make the sample data even more manageable by keeping all the scores within even tighter boundaries, say 500 to 599. For purposes of testing, score_range should have only the rows actually used, plus perhaps one more (like 450-499 above) to prove that unmatched ranges are being handled correctly.

    The output will be a lot more complicated if you add other dimension columns, such as college and status. That's why it's important to keep the sample data as simple as possible, and to post exactly what you want as output.

    In the example above, the sub-query all_colleges isn't used for anything. Depending on what you really want for output, you may not need it either. On the other hand, you may need another sub-query, all_statuses, in additioon to it.

    The sample output you posted had averages on a separate row. Is that negotiable? It might be easier to produce (and much less confusing to read) if the averages appeared side by side with the counts, as in the output I posted above. I went to extra trouble to keep the averages from appearing for each each individual score range; chances are you'll need to do something similar.
  • SanjayRs
    SanjayRs Member Posts: 3,024 Silver Trophy
    Employee Table Example
    SQL> with data as (
      2  select job,
      3  decode(job,'ANALYST',
      4  count(*) over (partition by job )) Analyst,
      5  decode(job,'CLERK',
      6  count(*) over (partition by job )) clerk,
      7  decode(job,'MANAGER',
      8  count(*) over (partition by job )) manager,
      9  decode(job,'SALESMAN',
     10  count(*) over (partition by job )) salesman
     11  from emp
     12  )
     13  select
     14          max(Analyst) Analyst
     15          ,max(Clerk) Clerk
     16          ,max(Manager) Manager
     17          ,max(Salesman) Salesman
     18  from data
     19
    SQL> /
    
       ANALYST      CLERK    MANAGER   SALESMAN
    ---------- ---------- ---------- ----------
             2          4          3          4
    SS
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 6, 2009 4:24PM
    Hi,

    Simpler employee table example:
    SELECT	COUNT (CASE WHEN job = 'ANALYST'	THEN 1 END)	AS analyst
    ,	COUNT (CASE WHEN job = 'CLERK'		THEN 1 END)	AS clerk
    ,	COUNT (CASE WHEN job = 'MANAGER'	THEN 1 END)	AS manager
    ,	COUNT (CASE WHEN job = 'SALESMAN'	THEN 1 END)	AS salesman
    FROM	scott.emp;
    Output:
    &nbsp;  ANALYST      CLERK    MANAGER   SALESMAN
    ---------- ---------- ---------- ----------
             2          4          3          4
    Pivot by MIN (or MAX) only when the data is already aggregated; otherwise, pivot by the appropriate function. (Actually, MIN or MAX could be the appropriate function, but, in practice, it hardly ever is.)

    More relevant employee table example:
    SELECT	s.grade
    ,	COUNT (CASE WHEN e.job = 'ANALYST'	THEN 1 END)	AS analyst
    ,	COUNT (CASE WHEN e.job = 'CLERK'	THEN 1 END)	AS clerk
    ,	COUNT (CASE WHEN e.job = 'MANAGER'	THEN 1 END)	AS manager
    ,	COUNT (CASE WHEN e.job = 'SALESMAN'	THEN 1 END)	AS salesman
    FROM	      	 scott.salgrade	 s
    LEFT OUTER JOIN  scott.emp	 e	ON e.sal	BETWEEN  s.losal
         	   	 		 	   		AND	 s.hisal
    GROUP BY  CUBE (s.grade)
    ORDER BY  s.grade;
    Output:
    &nbsp;    GRADE    ANALYST      CLERK    MANAGER   SALESMAN
    ---------- ---------- ---------- ---------- ----------
             1          0          3          0          0
             2          0          1          0          2
             3          0          0          0          2
             4          2          0          3          0
             5          0          0          0          0
                        2          4          3          4
    But neither of these addresses OP's other problem: presenting the three columns in the original table as three independent measures, all outer joined to the same table.
  • 708388
    708388 Member Posts: 85
    Hi Frank,

    It seems that your query/results are what I'm looking for. I tried to run it, but received an error (I'm running in Toad) ORA-32035: unreferenced query name defined in WITH clause.
    I was able to run some of the pieces in the WITH section independently and they seem to work.
    However, still trying to understand the overall query, so not sure what might be the issue.
    This is the line that gets highlighted after I click ok on the error message:

    FROM score_range r

    I copied the section below and commented out the case statements and ran that portion only and it seems to work stand alone, so I don't think that score_range is the error.

    This works
    SELECT      r.display_txt
    --,      COUNT              (CASE WHEN u.test_code = 1 THEN 1     END)    AS read 
    --,      CASE
    --        WHEN  GROUPING (display_txt) = 1
    --        THEN  ROUND ( AVG (CASE WHEN u.test_code = 1 THEN score END)
    --                      , 2
    --                )
    --      END                                    AS read_avg
    --,      COUNT           (CASE WHEN u.test_code = 2 THEN 1     END)    AS math
    --,      CASE
    --        WHEN  GROUPING (display_txt) = 1
    --        THEN  ROUND ( AVG (CASE WHEN u.test_code = 2 THEN score END)
    --                      , 2
    --                )
    --      END                                    AS math_avg
    --,      COUNT           (CASE WHEN u.test_code = 3 THEN 1     END)    AS write 
    --,      CASE
    --        WHEN  GROUPING (display_txt) = 1
    --        THEN  ROUND ( AVG (CASE WHEN u.test_code = 3 THEN score END)
    --                      , 2
    --                )
    --      END                                    AS write_avg
    FROM                  score_range     r
    As requested, here are sample data:
    with gpa_stat as
    (
    select '12345678' ID, '200940' Academic_period, 'Freshmen' Stu_Pop, 'F' Stu_population, 'NS' College, 1 Applied, 1 Accepted, 1 Deposit, 560 SAT_READ, 490 SAT_MATH, 510 SAT_WRITE from dual union all
    select '23456789', 	'200940',	'Transfer',	'T', 	'LA',	1,	1,	0,  null, null, null    from dual union all			
    select '34567890',	'200940',	'Freshmen',	'F',	'BN',	1,	1,	1,	600,	610,	540 from dual union all
    select '45678901',	'200940',	'Freshmen',	'F',	'NS',	1,	1,	1,	530,	620,	630 from dual union all
    select '56789012',	'200940',	'Freshmen',	'F',	'NS',	1,	1,	1,	550,	420,	540 from dual union all
    select '67890123',	'200940',	'Freshmen',	'F',	'LA',	1,	1,	1,	null,	null,  null from dual 
    )
    Here are results when using the op query run one at a time for each category (Read, Math, Write). Only showing the overall total so as not to display all the college totals
    STU_POP	STATUS	COLLEGE	SCORERANGE	COUNT	AVGREAD
    
    Freshmen	1Applied		001 - 299	0	0
    Freshmen	1Applied		300 - 349	0	0
    Freshmen	1Applied		350 - 399	0	0
    Freshmen	1Applied		400 - 449	0	0
    Freshmen	1Applied		450 - 499	0	0
    Freshmen	1Applied		500 - 549	1	530
    Freshmen	1Applied		550 - 599	2	555
    Freshmen	1Applied		600 - 649	1	600
    Freshmen	1Applied		650 - 699	0	0
    Freshmen	1Applied		700 - 749	0	0
    Freshmen	1Applied		750 - 800	0	0
    Freshmen	1Applied		No Scores	1	0
    Freshmen	1Applied			5	560
    
    STU_POP	STATUS	COLLEGE	SCORERANGE	COUNT	AVGMATH
    					
    Freshmen	1Applied		001 - 299	0	0
    Freshmen	1Applied		300 - 349	0	0
    Freshmen	1Applied		350 - 399	0	0
    Freshmen	1Applied		400 - 449	1	420
    Freshmen	1Applied		450 - 499	1	490
    Freshmen	1Applied		500 - 549	0	0
    Freshmen	1Applied		550 - 599	0	0
    Freshmen	1Applied		600 - 649	2	615
    Freshmen	1Applied		650 - 699	0	0
    Freshmen	1Applied		700 - 749	0	0
    Freshmen	1Applied		750 - 800	0	0
    Freshmen	1Applied		No Scores	1	0
    Freshmen	1Applied			5	535
    
    STU_POP	STATUS	COLLEGE	SCORERANGE	COUNT	AVGWRITE
    					
    Freshmen	1Applied		001 - 299	0	0
    Freshmen	1Applied		300 - 349	0	0
    Freshmen	1Applied		350 - 399	0	0
    Freshmen	1Applied		400 - 449	0	0
    Freshmen	1Applied		450 - 499	0	0
    Freshmen	1Applied		500 - 549	3	530
    Freshmen	1Applied		550 - 599	0	0
    Freshmen	1Applied		600 - 649	1	630
    Freshmen	1Applied		650 - 699	0	0
    Freshmen	1Applied		700 - 749	0	0
    Freshmen	1Applied		750 - 800	0	0
    Freshmen	1Applied		No Scores	1	0
    Freshmen	1Applied			5	555
    Appologies if this still isn't what you are requesting of me in your first response.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Jul 6, 2009 10:42PM
    Hi,
    user1069723 wrote:
    Hi Frank,

    It seems that your query/results are what I'm looking for. I tried to run it, but received an error (I'm running in Toad) ORA-32035: unreferenced query name defined in WITH clause.
    The unreferenced query defined in the WITH clause is all_colleges. My version of Oracle doesn't mind it; yours apparantly does. Since you're not using all_colleges, delete it.

    ...
    As requested, here are sample data:
    with gpa_stat as
    (
    select '12345678' ID, '200940' Academic_period, 'Freshmen' Stu_Pop, 'F' Stu_population, 'NS' College, 1 Applied, 1 Accepted, 1 Deposit, 560 SAT_READ, 490 SAT_MATH, 510 SAT_WRITE from dual union all
    select '23456789', 	'200940',	'Transfer',	'T', 	'LA',	1,	1,	0,  null, null, null    from dual union all			
    select '34567890',	'200940',	'Freshmen',	'F',	'BN',	1,	1,	1,	600,	610,	540 from dual union all
    select '45678901',	'200940',	'Freshmen',	'F',	'NS',	1,	1,	1,	530,	620,	630 from dual union all
    select '56789012',	'200940',	'Freshmen',	'F',	'NS',	1,	1,	1,	550,	420,	540 from dual union all
    select '67890123',	'200940',	'Freshmen',	'F',	'LA',	1,	1,	1,	null,	null,  null from dual 
    )
    Here are results when using the op query run one at a time for each category (Read, Math, Write). Only showing the overall total so as not to display all the college totals
    If you want to display all the college totals, post results that have them displayed.
    The output is liable to be quite long. Can you cut out all but one of the unused ranges for now? It will make reading and understanding your output much easier.

    I see now that you want the test types on separate lines, first all of one type, then all of another, fianally of the other. That's quite different from what I imagined before. Unless I see how you want the output to look with all the colleges and whatever other options you want, I can't guess how to get it.

    Do you really want titles and blank lines in the middle of the output? If so, it may be much easier to get them using your front-end tool than using SQL. Are you planning to run this in Toad?
    ...
    Appologies if this still isn't what you are requesting of me in your first response.
    That's five rows of data, and the results you would want from those five rows. In that sense, it's exactly what I requested.
    If your real output will have more than one stu_population, then your sample data should include at least two distinct stu_populations, and your output should show how you want them displayed, and especially what kind of totals you want.
    The same goes for colleges and status. Include two of each in the data and output if they effect how the output is to look.
  • 708388
    708388 Member Posts: 85
    Hi Frank,

    Thanks again for your patience and willingness to help.

    I removed the all_colleges section and the query ran, but the results did not match what I was expecting. If you run the sample data with the original query (reposted below), you will see the results. The sample data contains 2 populations (Freshmen "F" and Transfer "T") it also contains 3 colleges ("LA", "BN" and "NS") and 3 different status types (Applied, Accepted and Deposit) a person can have one two or all three status at the same time.
    with gpa_stat as
    (
    select '12345678' ID, '200940' Academic_period, 'Freshmen' Stu_Pop, 'F' Stu_population, 'NS' College, 1 Applied, 1 Accepted, 1 Deposit, 560 SAT_READ, 490 SAT_MATH, 510 SAT_WRITE from dual union all
    select '23456789',     '200940',    'Transfer',    'T',     'LA',    1,    1,    0,  null, null, null    from dual union all            
    select '34567890',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    600,    610,    540 from dual union all
    select '45678901',    '200940',    'Freshmen',    'F',    'NS',    1,    1,    1,    530,    620,    630 from dual union all
    select '56789012',    '200940',    'Freshmen',    'F',    'NS',    1,    1,    1,    550,    420,    540 from dual union all
    select '67890123',    '200940',    'Freshmen',    'F',    'LA',    1,    1,    1,    null,    null,  null from dual 
    )
    
    , interesting_score_stat AS
    (
        SELECT stu_population, '1Applied' Status, college,
               sat_read, sat_math, sat_write
        FROM   gpa_stat
        WHERE  stu_population  in ('F','T')
        AND    academic_period = '200940'
    UNION ALL
        SELECT stu_population, '2Accepted' Status, college,
               sat_read, sat_math, sat_write
        FROM   gpa_stat
        WHERE  stu_population  in ('F','T')
        AND    academic_period = '200940'
        AND    accepted = 1
    UNION ALL
        SELECT stu_population, '3Deposit' Status, college,
               sat_read, sat_math, sat_write
        FROM   gpa_stat
        WHERE  stu_population  in ('F','T')
        AND    academic_period = '200940'
        AND    accepted = 1
        AND    deposit  = 1
    ),       all_colleges      AS
    (
        SELECT DISTINCT stu_population, Status, college
        FROM   interesting_score_stat
        
    )
    SELECT c.stu_population, 
           c.Status,
           c.college,
           r.display_txt           AS scorerange,
           COUNT (s.college)       AS count,
           round(NVL(AVG(s.sat_read),0),0)  AS avgRead
    --       round(NVL(AVG(s.sat_math),0),0)  AS avgMath,
    --       round(NVL(AVG(s.sat_write),0),0) AS avgWrite
    FROM  all_colleges           c
    CROSS JOIN score_range         r
    LEFT OUTER JOIN interesting_score_stat s    
      ON ( c.stu_population   = s.stu_population
     AND   c.status    = s.status
     AND   c.college   = s.college
     AND   r.low_score  <= s.sat_read
     --AND   r.low_score  <= s.sat_math
     --AND   r.low_score  <= s.sat_write
     AND   r.high_score  > s.sat_read
    -- AND   r.high_score  > s.sat_math
    -- AND   r.high_score  > s.sat_write
         )
      OR ( c.stu_population  =  s.stu_population 
     AND   c.status   =  s.status
     AND   c.college  =  s.college
     AND   r.low_score     IS NULL
     AND   s.sat_read      IS NULL
    -- AND   s.sat_math      IS NULL
    -- AND   s.sat_write     IS NULL
         )
    GROUP BY c.stu_population, 
             c.status, 
             cube ( c.college, 
                    r.display_txt
                  )
    ORDER BY c.stu_population, 
             c.status, 
             c.college, 
             r.display_txt
     
    ;
    Here is the output. I left the row numbers, so that if the above query is run, you can see where they are coming from. I have run the query 3 times once for each type and then pasted them together how the final output would look like if I were able to have them side by side.
    Row#	STU_POPULATION	STATUS	COLLEGE	SCORERANGE	READ	AVGREAD	Math	AvgMath	Write	AvgWrite
    										
    85	F	2Accepted	NS	550 - 599	2	555	0	0	0	0
    86	F	2Accepted	NS	600 - 649	0	0	1	620	1	630
    87	F	2Accepted	NS	650 - 699	0	0	0	0	0	0
    88	F	2Accepted	NS	700 - 749	0	0	0	0	0	0
    89	F	2Accepted	NS	750 - 800	0	0	0	0	0	0
    90	F	2Accepted	NS	No Scores	0	0	0	0	0	0
    91	F	2Accepted	NS	( Total )	3	547	3	510	3	560
    92	F	2Accepted		001 - 299	0	0	0	0	0	0
    93	F	2Accepted		300 - 349	0	0	0	0	0	0
    180	T	1Applied		750 - 800	0	0	0	0	0	0
    181	T	1Applied		No Scores	1	0	1	0	1	0
    182	T	1Applied		( Total )	1	0	1	0	1	0
    183	T	2Accepted	LA	001 - 299	0	0	0	0	0	0
    184	T	2Accepted	LA	300 - 349	0	0	0	0	0	0
    Note: ( Total ) is not in the actual output, I put that in so that the numbers on that row would be more
    in alignment with the other numbers as on my screen they shifted way to the left.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    Hi,
    user1069723 wrote:
    ...
    Here is the output. I left the row numbers, so that if the above query is run, you can see where they are coming from. I have run the query 3 times once for each type and then pasted them together how the final output would look like if I were able to have them side by side.
    I'm not sure I understand. Are you saying that, from these 5 rows of data, you want to produce 184 rows (or more) of output?

    I need the exact output you want from the sample data. If the sample data has
    3 ranges (and you want to include one more range, just to make sure such rows are shown),
    2 stu_populations,
    2 colleges and
    2 statuses,
    then you should be able to post complete sample output that contains (3+1)*2*2*2=32 rows, plus whatever totals you want.

    Did you read my previous answers? It doesn't look like you've applied anything in them to your query.
  • 708388
    708388 Member Posts: 85
    Hi Frank,

    The reason I went back to the original query was because I was able to get the results in order for you to see if you ran that query how it would look, at least for one set (Read and AvgRead). The final output would have:

    13 rows per college (each score range, no score and a total) -- currently there are 4 colleges for Freshmen/Transfer
    13 rows summary for total of all colleges within Freshmen and within Transfer
    3 score types (READ, MATH, WRITE) --perhaps in the future there could be more
    3 status types (Applied, Accepted, Deposit) -- end of semester deposit is replaced with enrolled


    Output Example.
    Row#	STU_POPULATION	STATUS	COLLEGE	SCORERANGE	READ	AvgREAD	MATH	AvgMath	WRITE	AVGWRITE
    
    1	F	1Applied	BN	001 - 299	0	0	0	0	0	0
    2	F	1Applied	BN	300 - 349	0	0	0	0	0	0
    3	F	1Applied	BN	350 - 399	0	0	0	0	0	0
    4	F	1Applied	BN	400 - 449	0	0	0	0	0	0
    5	F	1Applied	BN	450 - 499	0	0	0	0	0	0
    6	F	1Applied	BN	500 - 549	0	0	0	0	1	540
    7	F	1Applied	BN	550 - 599	0	0	0	0	0	0
    8	F	1Applied	BN	600 - 649	1	600	1	610	0	0
    9	F	1Applied	BN	650 - 699	0	0	0	0	0	0
    10	F	1Applied	BN	700 - 749	0	0	0	0	0	0
    11	F	1Applied	BN	750 - 800	0	0	0	0	0	0
    12	F	1Applied	BN	No Scores	0	0	0	0	0	0
    13	F	1Applied	BN		1	600	1	610	1	540
    14	F	1Applied	LA	001 - 299	0	0
    15	F	1Applied	LA	300 - 349	0	0.....
    After BN would come the next college "LA" and so on then a total of all 4 colleges. Once all the "F" "1Applieds" are done, it would then show the "F" "2Accept" for the colleges then the "F" "3Deposit", then would change to do the same for the "T" "1Applied", etc.

    Below is the other query you provided with the changes I made. The counts and averages are too low with this query, so not sure what I did wrong. The total is off by 1185 people for "F" "1Applied" "BN"
    with   cntr       AS
    (
        SELECT    LEVEL    AS test_code
        FROM    dual
        CONNECT BY    LEVEL <= 3    -- # of measure columns (sat_read, sat_math and sat_write)
    )
    ,    unpivoted_data        AS
    (
        SELECT    s.college
        ,    s.stu_population
        ,    CASE
                WHEN  accepted = 1
                AND   deposit  = 1
                           THEN    '3 Deposit'
                WHEN  accepted = 1
                           THEN    '2 Accepted'
                           ELSE    '1 Applied'
            END    AS status
    --    ,    ...                -- plus anything from s that you need later on    
        ,    c.test_code
        ,    CASE    c.test_code
                WHEN  1        THEN  sat_read
                WHEN  2        THEN  sat_math
                WHEN  3        THEN  sat_write
            END    AS score
        FROM        gpa_stat    s
        CROSS JOIN    cntr        c
        WHERE   stu_population        IN ('F', 'T')    -- Do all filtering here
        AND    academic_period        = '200940'
    )
    
    SELECT      stu_population, status, college, r.display_txt
    ,      COUNT              (CASE WHEN u.test_code = 1 THEN 1     END)    AS read 
    ,      CASE
            WHEN  GROUPING (display_txt) = 1
            THEN  ROUND ( AVG (CASE WHEN u.test_code = 1 THEN score END)
                          , 2
                    )
          END                                    AS read_avg
    ,      COUNT           (CASE WHEN u.test_code = 2 THEN 1     END)    AS math
    ,      CASE
            WHEN  GROUPING (display_txt) = 1
            THEN  ROUND ( AVG (CASE WHEN u.test_code = 2 THEN score END)
                          , 2
                    )
          END                                    AS math_avg
    ,      COUNT           (CASE WHEN u.test_code = 3 THEN 1     END)    AS write 
    ,      CASE
            WHEN  GROUPING (display_txt) = 1
            THEN  ROUND ( AVG (CASE WHEN u.test_code = 3 THEN score END)
                          , 2
                    )
          END                                    AS write_avg
    FROM                  score_range     r
    LEFT OUTER JOIN     unpivoted_data  u    ON (    u.score    >=  r.low_score
                                           AND    u.score <   r.high_score
                           )
                        OR (    u.score         IS NULL
                           AND    r.low_score     IS NULL
                           )
    GROUP BY  CUBE ( stu_population, status, college, r.display_txt
               )
    ORDER BY  stu_population, status, college, GROUPING ( display_txt)
    ,           MIN (r.range_sort)
    ;
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    Answer ✓
    Hi,

    To make more manageable sample data, I cut socre_range down to four rows:
    SELECT  450 AS low_score, 500 AS high_score, '450 - 499' AS display_txt, 5  AS range_sort    FROM dual    UNION ALL
    SELECT  500 AS low_score, 550 AS high_score, '500 - 549' AS display_txt, 6  AS range_sort    FROM dual    UNION ALL
    SELECT  550 AS low_score, 600 AS high_score, '550 - 599' AS display_txt, 7  AS range_sort    FROM dual    UNION ALL
    SELECT  NULL,          NULL,           'No Scores',                      13           FROM dual;
    and used this data in gpa_stat:
    select '12345678' ID, '200940' Academic_period, 'Freshmen' Stu_Pop, 'F' Stu_population, 'LA' College, 1 Applied, 1 Accepted, 1 Deposit, 560 SAT_READ, 590 SAT_MATH, 510 SAT_WRITE from dual union all
    select '23456789',     '200940',    'Transfer',    'T',     'LA',    1,    1,    0,  null, null, null    from dual union all            
    select '34567890',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    500,    510,    540 from dual union all
    select '45678901',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    530,    520,    630 from dual union all
    select '56789012',    '200940',    'Freshmen',    'F',    'BN',    1,    1,    1,    550,    520,    540 from dual union all
    select '67890123',    '200940',    'Freshmen',    'F',    'LA',    1,    1,    1,    null,    null,  null from dual 
    This query:
    WITH   cntr	   AS
    (
    	SELECT	LEVEL	test_code
    	FROM	dual
    	CONNECT BY	LEVEL <= 3	-- # of measure columns (sat_read, sat_math and sat_write)
    )
    ,	unpivoted_data	    AS
    (
    	SELECT	s.stu_population
    	,	CASE
    			WHEN  accepted = 1
    			AND   deposit  = 1
    				       THEN	3
    			WHEN  accepted = 1
    				       THEN	2
    				       ELSE	1
    		END	AS status_lvl
    	,	s.college
    	,	c.test_code
    	,	CASE	c.test_code
    			WHEN  1		THEN  sat_read
    			WHEN  2		THEN  sat_math
    			WHEN  3		THEN  sat_write
    		END	AS score
    	FROM		gpa_stat	s
    	CROSS JOIN	cntr		c
    	WHERE   stu_population		IN ('F', 'T')	-- Do all filtering here
    	AND	academic_period		= '200940'
    )
    ,	all_colleges	AS
    (
    	SELECT DISTINCT	college
    	FROM   		unpivoted_data
    )
    ,	all_populations	AS
    (	SELECT DISTINCT	stu_population
    	FROM   		unpivoted_data
    )
    SELECT	  p.stu_population
    ,	  a.display_txt						AS status
    ,	  c.college
    ,	  NVL ( r.display_txt
    	      , ' (Total)'
    	      )							AS scorerange
    ,	  COUNT	(CASE WHEN u.test_code = 1 THEN 1     END)	AS read 
    ,	  AVG 	(CASE WHEN u.test_code = 1 THEN score END)	AS avgread
    ,	  COUNT (CASE WHEN u.test_code = 2 THEN 1     END)	AS math
    ,	  AVG 	(CASE WHEN u.test_code = 2 THEN score END)	AS avgmath
    ,	  COUNT (CASE WHEN u.test_code = 3 THEN 1     END)	AS write 
    ,	  AVG 	(CASE WHEN u.test_code = 3 THEN score END)	AS avgwrite
    FROM	      	    all_populations p
    CROSS JOIN	    all_status	    a
    CROSS JOIN	    all_colleges    c
    CROSS JOIN	    score_range     r
    LEFT OUTER JOIN     unpivoted_data  u	ON  	u.stu_population  =  p.stu_population
         	   	    		    	AND	u.status_lvl	  >= a.lvl_id
         	   	    		    	AND 	(	(	  u.score      >=  r.low_score
         	   	    		    	   		AND	  u.score      <   r.high_score
    					   		)
    						OR	(	  u.score      IS NULL
    					   		AND	  r.low_score  IS NULL
    					   		)
    					    	)
    					AND	u.college	  =  c.college
    GROUP BY  p.stu_population
    ,     	  a.display_txt
    ,	  c.college
    ,	  ROLLUP ((r.display_txt))
    ORDER BY  p.stu_population
    ,     	  a.display_txt
    ,         c.college
    ,	  GROUPING (r.display_txt)
    ,     	  MIN (r.range_sort)
    ;
    produces this output:
    STU STATUS    CO SCORERANG READ READ MATH MATH WRITE WRITE
    --- --------- -- --------- ---- ---- ---- ---- ----- -----
    F   1Applied  BN 450 - 499    0         0          0
    F   1Applied  BN 500 - 549    2  515    3  517     2   540
    F   1Applied  BN 550 - 599    1  550    0          0
    F   1Applied  BN No Scores    0         0          0
    F   1Applied  BN  (Total)     3  527    3  517     2   540
    F   1Applied  LA 450 - 499    0         0          0
    F   1Applied  LA 500 - 549    0         0          1   510
    F   1Applied  LA 550 - 599    1  560    1  590     0
    F   1Applied  LA No Scores    1         1          1
    F   1Applied  LA  (Total)     2  560    2  590     2   510
    F   2Accepted BN 450 - 499    0         0          0
    F   2Accepted BN 500 - 549    2  515    3  517     2   540
    F   2Accepted BN 550 - 599    1  550    0          0
    F   2Accepted BN No Scores    0         0          0
    F   2Accepted BN  (Total)     3  527    3  517     2   540
    F   2Accepted LA 450 - 499    0         0          0
    F   2Accepted LA 500 - 549    0         0          1   510
    F   2Accepted LA 550 - 599    1  560    1  590     0
    F   2Accepted LA No Scores    1         1          1
    F   2Accepted LA  (Total)     2  560    2  590     2   510
    F   3Deposit  BN 450 - 499    0         0          0
    F   3Deposit  BN 500 - 549    2  515    3  517     2   540
    F   3Deposit  BN 550 - 599    1  550    0          0
    F   3Deposit  BN No Scores    0         0          0
    F   3Deposit  BN  (Total)     3  527    3  517     2   540
    F   3Deposit  LA 450 - 499    0         0          0
    F   3Deposit  LA 500 - 549    0         0          1   510
    F   3Deposit  LA 550 - 599    1  560    1  590     0
    F   3Deposit  LA No Scores    1         1          1
    F   3Deposit  LA  (Total)     2  560    2  590     2   510
    
                                     AVG       AVG         AVG
    STU STATUS    CO SCORERANG READ READ MATH MATH WRITE WRITE
    --- --------- -- --------- ---- ---- ---- ---- ----- -----
    T   1Applied  BN 450 - 499    0         0          0
    T   1Applied  BN 500 - 549    0         0          0
    T   1Applied  BN 550 - 599    0         0          0
    T   1Applied  BN No Scores    0         0          0
    T   1Applied  BN  (Total)     0         0          0
    T   1Applied  LA 450 - 499    0         0          0
    T   1Applied  LA 500 - 549    0         0          0
    T   1Applied  LA 550 - 599    0         0          0
    T   1Applied  LA No Scores    1         1          1
    T   1Applied  LA  (Total)     1         1          1
    T   2Accepted BN 450 - 499    0         0          0
    T   2Accepted BN 500 - 549    0         0          0
    T   2Accepted BN 550 - 599    0         0          0
    T   2Accepted BN No Scores    0         0          0
    T   2Accepted BN  (Total)     0         0          0
    T   2Accepted LA 450 - 499    0         0          0
    T   2Accepted LA 500 - 549    0         0          0
    T   2Accepted LA 550 - 599    0         0          0
    T   2Accepted LA No Scores    1         1          1
    T   2Accepted LA  (Total)     1         1          1
    T   3Deposit  BN 450 - 499    0         0          0
    T   3Deposit  BN 500 - 549    0         0          0
    T   3Deposit  BN 550 - 599    0         0          0
    T   3Deposit  BN No Scores    0         0          0
    T   3Deposit  BN  (Total)     0         0          0
    T   3Deposit  LA 450 - 499    0         0          0
    T   3Deposit  LA 500 - 549    0         0          0
    T   3Deposit  LA 550 - 599    0         0          0
    T   3Deposit  LA No Scores    0         0          0
    T   3Deposit  LA  (Total)     0         0          0
    
    60 rows selected.
    Among the assumptions I made were some about status.
    The different status values of status seem to be subsets of one another. That is, any row that is qualifies as '3Deposit' is also counted as '2Accepted', and any row that is counted as '2Accepted' is also counted as '1Applied'.
    For the same reason that you should have a scre_range table, you should also have a table for theses statuses, like this:
    CREATE TABLE  all_status
    AS
    SELECT	1 as lvl_id, '1Applied' AS display_txt	FROM dual	UNION ALL
    SELECT	2,   	     '2Accepted'   		FROM dual	UNION ALL
    SELECT	3,	     '3Deposit'			FROM dual;
    where the higher lvl_ids are assumed to include the lower ones (for example, lvl_id 1 is a subset of 2 and 3).

    When there are no rows in a group, the average columns in the output above are left NULL.
    If you want 0's in those places, use "NVL (AVG (...), 0)" instead of "AVG (...)".
  • 708388
    708388 Member Posts: 85
    Outstanding. Thank you so much.
This discussion has been closed.