CODEWITHSUNDEEP
sql
Lunae Lumen
Lunae Lumen

Reputation: 15

How to count with cases without subqueries in this situation

I have a table grades[Student, Grade] My task is to сount the amount of students, have 2 A's (2) and at least one D (2). The most painful part is that i shouldn't use sql subqueries

That abomination i come up with would only list such nulls and students which fit by condition Any ideas how to count them ? Or rework query?

SELECT CASE WHEN 
            (
              sum(CASE WHEN g1.grade = 5 
                     THEN 1
                     ELSE 0 
                     END) > 2
              
            )
            AND
            (
              sum(CASE WHEN g1.grade = 2 
                     THEN 1
                     ELSE 0 
                     END) > 0              
            )
            THEN g1.student_name
            ELSE NULL
            END as student_name
            
FROM grades g1
group by g1.student_name

Upvotes: 0

Views: 40

Answers (2)

Stu
Stu

Reputation: 32579

You don't need any sub queries you can just count and filter using having and conditional sum

select Student
from Grades
group by Student
having sum(case when Grade='A' then 1 end)=2
and sum(case when Grade='D' then 1 end)>0

Edit

To get just the count of qualifying rows you can do

select distinct count(*) over()
from Grades
group by Student
having sum(case when Grade='A' then 1 end)=2
and sum(case when Grade='D' then 1 end)>0

Upvotes: 2

Del
Del

Reputation: 1599

Oh how I miss homework.... Depending on your database, you can use PIVOT:

SELECT *
FROM grades PIVOT (COUNT(*) AS CNT FOR grade IN (5 AS A, 2 AS D)) g
WHERE g.a_cnt >= 2 AND g.d_cnt > 0;

Alternate:

SELECT g.student
FROM grades g
GROUP BY g.student
HAVING COUNT(DECODE(g.grade, 5, 1, NULL)) >= 2 AND COUNT(DECODE(g.grade, 2, 1, NULL)) > 0;

Upvotes: 1

Related Questions