Basssque
asked on
using case when with count and partition by functions in an oracle query
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"
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"
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
...
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
...
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
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
)
/
ASKER
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
)
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
)
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