Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Need to simplify this query

Posted on 2013-06-07
Medium Priority
429 Views
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
``````
0
Question by:Barry62
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 4
• 4
• +2

LVL 29

Expert Comment

ID: 39230308
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

LVL 8

Author Comment

ID: 39230462
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

LVL 29

Expert Comment

ID: 39230487
And...
. . .
3) Expected results (based on your data)
0

LVL 8

Author Comment

ID: 39230549
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

LVL 35

Expert Comment

ID: 39231392
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

LVL 8

Author Comment

ID: 39231606
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

LVL 32

Expert Comment

ID: 39231799
>>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

LVL 8

Author Comment

ID: 39231990
I am only testing for 'FA' 2010-2013 right now.
0

LVL 29

Expert Comment

ID: 39232396
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

LVL 8

Author Comment

ID: 39232481
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

LVL 35

Expert Comment

ID: 39232516
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

LVL 29

Accepted Solution

MikeOM_DBA earned 1000 total points
ID: 39233327
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
``````
:?
0

LVL 8

Author Comment

ID: 39233816
@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

LVL 35

Assisted Solution

James0628 earned 1000 total points
ID: 39233849
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

LVL 8

Author Comment

ID: 39242515
yes, they are
0

LVL 41

Expert Comment

ID: 39243099
What is your database and version? MySQL or Oracle?
0

LVL 35

Expert Comment

ID: 39243817
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

LVL 8

Author Comment

ID: 39244280
@Sharath - It is actually Informix, which I guess is closest to Oracle?

@James & Mike - I will let you  (both) know how it goes.
0

LVL 8

Author Comment

ID: 39256992
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logoâ€¦
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll