sql query to find student having average of 90 from last 2 years?-CodePudding

I was trying this on test table

create table years (
  yr bigint,
  average decimal(10,2),
  rollno bigint
)

i created this table year for storing 2 years like 2020 and 2021 average of marks scored in average

Condition is to find only those students whose avg is above 90 from last 2 years. data is as follows

year    average rollno
2021    55.20   1
2020    55.50   1
2020    54.50   2
2020    55.50   3
2021    55.40   3

select rollno 
from years
where average > 55
and yr = (YEAR(GETDATE())-1)
and yr = (YEAR(GETDATE())-2)

i tried this query but it is not working when i want to specifically find those values where the condition is true.

if i use this query like this

select rollno 
from years 
where average > 55
and yr = (YEAR(GETDATE())-1) or yr = (YEAR(GETDATE())-2)

it works but doesnt give me the desired result.

CodePudding user response：

Perhaps something like the following is what you are after?

This would result in rollno 1 & 3.

select rollno
from years
where yr in ( Year(DateAdd(year, -1, GetDate())), Year(DateAdd(year, -2, GetDate())))
group by rollno
having Sum(average) > 90;

CodePudding user response：

I'm checking your request after a review in you query, seems there are some parentheses missing in your expression.

Follows the example, based on your query

SELECT rollno FROM years WHERE 
    average > 55 
    and --below the bracket P1
    (--Open P1
        (--Open P2
            yr = year(getdate())-1
        )--Close P2 
        or
        (--Open P3
            yr = year(getdate())-2
        )--Close P3 
    )--Close P1

What does it mean in the WHERE clause is the average > 55(mandatory) AND all what we have inside in the bracket P1.

The result

rollno
1
1
3
3

Best Regards