?
Solved

oracle sql analytics

Posted on 2014-12-04
7
Medium Priority
?
73 Views
Last Modified: 2015-02-16
i have a table which records students preferences. i need to flag up the students who have only preferenced 20, 21 or 22.
the table below shows the output from the pref table (2 columns), and the output shows the desired output.

select * from pref			output					
								
student	preference		student	only_20_21_22				
1		1				1		n
1		2				2		n
1		3				3		y
2		1				4		n
2		2				5		y
3		20				6		n
4		21				7		n
4		22				8		y
4		1				9		n
4		2				10		n
5		20						
5		22						
5		21						
6		1						
6		2						
7		1						
7		20						
8		20						
9		20						
9		1						
9		2						
9		22						
10		2						
					
		

Open in new window


any help is appreciated
0
Comment
Question by:tonMachine100
[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
  • Learn & ask questions
7 Comments
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40480446
Try this:
Select student, CASE 
            WHEN preference = 20 or preference = 21 or preference = 22 
               THEN 'y' 
               ELSE 'n' 
       END as only_20_21_22 from pref

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480461
select p.student,
       case regexp_count(listagg(p.preference, ',') within group(order by p.student), '^20|^21|^22')
         when 1 then
          'y'
         else
          'n'
       end only_20_21_22
  from pref p
 group by p.student
 order by p.student;

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480464
@Ahmed Merghani: Please see the asker's expected results! Your statement will not work upon what he asked for...
0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40480578
Assuming the sample data provided is not a complete sample and the following data is possible:
student, preference
11,20
11,21
11,22
11,23

Open in new window


The above query will also fail.

Building on it try this:
select p.student,
	case when ltrim(regexp_replace(listagg(p.preference, ',') within group(order by p.student),'2[012]'),',') is null then 'y' else 'n' end 
  from pref p
 group by p.student
 order by p.student;

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480598
Yes, slightwv's regex will work better upon the (sample) data provided...
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 40480872
select student,
min(case when not regexp_like(preference,'2[012]') then 'n' else 'y' end) student_only_20_21_22
from pref
group by student
order by student;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40480890
Knew there had to be a cleaner way...
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question