Need to simplify this query

Hi, experts.

I am converting another ACE report to Argos, and this one is particularly hairy.  I am finding the average application rate of students for a three year period in each major.  Now, keeping in mind that any given year could have 0 registrations (i.e. no records) in the table.  I have come up with a working solution, but with 27 majors, this solution will easily give me 200+ lines of SQL for one report.  Can anyone simplify it for me?

case
when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
)/3

else null
end actappavg

Open in new window

LVL 8
Barry62Asked:
Who is Participating?
 
MikeOM_DBACommented:
Why not just this:
WITH mytab AS
  (SELECT 2010 plan_enr_yr,
    'FA' plan_enr_sess,
    'ACT1' major,
    100 applied_cnt,
    36 accepted_cnt
  FROM dual UNION
  SELECT 2010,'FA','ACT3',40 ,18 FROM dual  UNION
  SELECT 2012,'FA','ACT2',30 ,10 FROM dual  UNION
  SELECT 2011,'SP','AMT ',150 ,100 FROM dual
  )
SELECT plan_enr_sess ,
  SUM(applied_cnt) app_cnt ,
  SUM(accepted_cnt) acc_cnt ,
  ROUND((SUM(accepted_cnt)*100/SUM(applied_cnt)),3) kk_pct
FROM mytab
GROUP BY plan_enr_sess

PLAN_ENR_SESS APP_CNT                ACC_CNT                KK_PCT                    
------------- ---------------------- ---------------------- ---------------------- 
SP            150                    100                    66.667                 
FA            170                    64                     37.647                 

2 rows selected

Open in new window

:?
0
 
MikeOM_DBACommented:
In order for us to help you, you must post a test case as follows:

1) Create table(s) statements
2) Insert statements with the test data
3) Expected results
4) Explanation of the requirements and rules that lead to that result.

:p
0
 
Barry62Author Commented:
OK, well I am not inserting any data.  This is just a for a report, so I am only doing SELECT's.

Here is some data:

lsrecruit_rec

plan_enr_yr      plan_enr_sess       major         applied_cnt          accepted_cnt
2010                       'FA'                'ACT1'                100                       36  
2010                       'FA'                'ACT3'                  40                       18
2012                       'FA'                'ACT2'                  30                       10
2011                       'SP'                'AMT '                 150                     100    

So I am needing to find the average rate of accepted students to applied students over a three year period for each major.  

The lsrecruit_rec is populated when the report is run.  It gathers admissions data from other tables and places it in this table, summing on the status of the student(Inquired,Applied,Accepted, etc.) based on their major and application year.  If the student's status is 'applied', the report adds it to the applied_cnt for that major.

The problem is that if there are no applications for a particular year/major, there is no record.  That's  why I need to test for no records in each year/major.  If there are records for 2010, and not for 2011 or 2012, hen I just display accepted_cnt/aplied_cnt for 2010.  If there are records for 2010 and 2012, but none for 2011, I average accepted_cnt/aplied_cnt for 2010 and accepted_cnt/aplied_cnt for 2012.  And so on and so on.  There are 8 combinations for each major.

See why I want to somplify it?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MikeOM_DBACommented:
And...
. . .
3) Expected results (based on your data)
0
 
Barry62Author Commented:
Based on the data I posted, I should get 37.64% for the combined majors ACT1, ACT2 and ACT3.  I should get 66.67% for AMT.
0
 
James0628Commented:
I haven't used MySQL, Oracle or Informix, so maybe it's just me, but FWIW ...

 I'm confused by something.  You have tests like this:

(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1

 Isn't that checking to see if the count (ie. number of rows) is 1?

 If so, in your sample data, you have two rows that match those conditions, so that test would fail, wouldn't it?


 Also, in your SQL, you use applied_cnt and applied_all, but in your sample data you have applied_cnt and accepted_cnt.

 James
0
 
Barry62Author Commented:
Sorry.  I changed a few things and posted old code.  The count queries should be testing for 0 and applied_all should be accepted_cnt.
0
 
awking00Commented:
>>Based on the data I posted, I should get 37.64% for the combined majors ACT1, ACT2 and ACT3.  I should get 66.67% for AMT.<<
Is that equivalent to I should get 37.64% for plan_enr_sess = 'FA'  I should get 66.67% for plan_enr_sess = 'SP'?
0
 
Barry62Author Commented:
I am only testing for 'FA' 2010-2013 right now.
0
 
MikeOM_DBACommented:
Sorry.  I changed a few things and posted old code.  The count queries should be testing for 0 and applied_all should be accepted_cnt.
And where is the NEW code?
0
 
Barry62Author Commented:
ummm....the NEW code is the OLD code with '= 0' for all of the count(*) queries, and 'accepted_cnt' in place of all the 'applied_all'.  I thought that was apparent.
0
 
James0628Commented:
Not to be difficult, but if you have " '= 0' for all of the count(*) queries", then you're just doing the same tests in each When.

 James
0
 
Barry62Author Commented:
@James - The code is testing for three different years per major.  So with production data, I have to test for count = 0 every time.  If I don't, I will get a divide by 0 error and the code blows up.

@Mike - Isn't your code just feeding data into the queries?  I am pulling data from the tables , not planting it.  Or maybe I'm missing your point?
0
 
James0628Commented:
In simplified form, the code in your first post has a Case with

when
((select count(*) ... where b.plan_enr_yr = 2010 ...) = 1 and
(select count(*) ... where b.plan_enr_yr = 2011 ...) = 0 and
(select count(*) ... where b.plan_enr_yr = 2012 ...) = 0)
then
<calculate average using only 2010>

 and then a When that checks if the count for 2010 is 0, 2011 is 1 and 2012 is 0 and, if so, calculates the average using only 2011; and so on, checking the various combinations of years with and without data.

 But then you said that you only have "= 0" now, which means that you're just doing the same tests in each When, which wouldn't make sense.  And looking for a 0 count for all 3 years wouldn't make sense either.

 So, I suspect that something got lost in translation somewhere.


 FWIW, as far as Mike's code goes, I think the first part was simply generating test data for the SELECT that starts at line 12.  Note the KK_PCT column in the results.  Are those the %'s that you're looking for?

 James
0
 
Barry62Author Commented:
yes, they are
0
 
SharathData EngineerCommented:
What is your database and version? MySQL or Oracle?
0
 
James0628Commented:
If the %'s from Mike's query are correct, then you could try his query (starting at line 12) and replace mytab with your table, and see what you get.  If it gives you what you need, great.  If not, let him (us) know what's wrong and maybe he (we) can fix it.

 James
0
 
Barry62Author Commented:
@Sharath - It is actually Informix, which I guess is closest to Oracle?

@James & Mike - I will let you  (both) know how it goes.
0
 
Barry62Author Commented:
Thanks, guys.  Mike's query worked.  I had to modify it a bit to separate the majors, but all of the percentages are correct with much less code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.