Solved

using case when with count and partition by functions in an oracle query

Posted on 2016-09-29
7
57 Views
Last Modified: 2016-09-30
I hope this makes sense.
I need to count 1 for each student who has a particular attendance code at least once.
I wrote the following statement but it doesn't seem to work as expected, I assume I'm not using the correct logic.  Can anyone help?

CASE WHEN Attendance_Code.Att_Code in ('HB') AND STUDENTS.grade_level = '12' THEN count(*) over (partition by students.student_number) end "12"
0
Comment
Question by:Basssque
7 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 41822613
without the rest of the query and without example data, expected result and real result  ?
a guess :  the count(*) counts over student , so wil count 2 when the students attents 2 times

to get 1  change every sum value other then 0 to 1  
example
select U.TABLE_NAME, COLUMN_NAME
,CASE CASE WHEN column_name like 'C%' THEN count(*) over (partition by column_name) ELSE 0 end
 WHEN 0 THEN 0
 ELSE 1
 END "CNT1"
,CASE WHEN column_name like 'C%' THEN count(*) over (partition by column_name) ELSE 0 end "CNT2"
from user_tab_columns U
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41822686
agree with flow01;
unless we know more about your data and the expected result we have to guess too much.

e.g.
a correlated subquery might be a reasonable solution here but I have no way of knowing if that is true

select
  s.studentid
,  (select 1 from Attendance_Code ac
        where s.studentid = ac.studentid and ac.Att_Code in ('HB') AND s.grade_level = '12' ) "12"
, ...
from STUDENTS s
...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41822706
perhaps a more traditional approach would suit you better?

SELECT
      s.studentid
    , s.grade_level
    , ac.hb
FROM students s
INNER JOIN (
      SELECT
            studentid
          , MAX(CASE WHEN Att_Code IN ('HB') THEN 1 ELSE 0 END) AS hb
      FROM Attendance_Code
      GROUP BY
            studentid
    ) ac ON s.studentid = ac.studentid

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41822750
Just like your previous question:
We aren't understanding your requirements.  Please create a simple test case that will show us what you are trying to
accomplish.

>> but it doesn't seem to work as expected

OK, what does it return that it shouldn't?  
What doesn't it return that it should?
Does it cause the server to reboot?

What does it do or not do based on your expectations?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41822774
Based on both questions is this what you are trying to solve?

For each grade, I want a count of the number of students that have at least one 'HB'?

See if this works for you(notice the very simple test case):
drop table tab1 purge;
create table tab1(student_number number, grade_level char(2), att_code varchar2(10));
insert into tab1 values(1,'11','HB');
insert into tab1 values(1,'12','HB');
insert into tab1 values(2,'10','XX');
insert into tab1 values(3,'10','HB');
insert into tab1 values(4,'12','HB');
insert into tab1 values(4,'12','HB');
insert into tab1 values(4,'12','HB');
insert into tab1 values(4,'12','HB');
commit;

select count(case when "12" > 0 then 1 end) total_for_12
from (
	select student_number, count(case when att_code='HB' and grade_level='12' then 1 end) "12"
	from tab1
	group by student_number
)
/

Open in new window


Student 1 has one HB in grade 12.  Student 4 has 4 HB in grade 12 so, for greade 12, you have TWO students that meet the requirement.

If it doesn't work ,please add whatever is necessary to the test case and provide expected results.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41822780
For 11 and 12:
select count(case when "11" > 0 then 1 end) total_for_11,
	count(case when "12" > 0 then 1 end) total_for_12
from (
	select student_number,
		count(case when att_code='HB' and grade_level='11' then 1 end) "11",
		count(case when att_code='HB' and grade_level='12' then 1 end) "12"
	from tab1
	group by student_number
)
/

Open in new window

0
 

Author Closing Comment

by:Basssque
ID: 41823686
slightvw

The following worked great!  Thanks so much!!!
select count(case when "12" > 0 then 1 end) total_for_12
from (
      select student_number, count(case when att_code='HB' and grade_level='12' then 1 end) "12"
      from tab1
      group by student_number
)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 93
Oracle Next Available Number 2 31
check the deletion of SQL job on who delete/disable it 12 28
help on oracle query 5 31
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now