I need to find the average of all records (Qty) which related Supplier numbers (SNo) if there have at least only one record

I need to find the average of all records (Qty) which related Supplier numbers (SNo) as S1, S2 ..etc. even if there have at least only one record to true the condition

this query select only one record and the query checking condition before get the average

select Sno, avg(qty) avg_qty                                              
from Supplier                                                       
group by  sno                             
having  avg(qty) > 50 and avg(qty) < 100

this is my table :

create table Supplier
(
SNo Varchar (5),
PNo Varchar (5),
JNo Varchar (5),
Qty int
)
Insert into Supplier values ('S1','P1','J1',50)
Insert into Supplier values ('S1','P1','J2',90)
Insert into Supplier values ('S1','P2','J1',40)
Insert into Supplier values ('S1','P3','J3',20)
Insert into Supplier values ('S2','P1','J3',110)
Insert into Supplier values ('S2','P2','J2',30)
Insert into Supplier values ('S2','P4','J3',10)
Insert into Supplier values ('S3','P3','J1',100)
Insert into Supplier values ('S3','P1','J3',80)
Insert into Supplier values ('S3','P4','J2',70)
Insert into Supplier values ('S4','P2','J1',60)
Insert into Supplier values ('S4','P1','J3',20)

I need to select average qty from the table which more than 50 & less than 100. but my query select only record only.
But I need to take result as:

S1 - 50 
S3 - 83 
S4 - 40 

So S2 haven’t any qty between 50 and 100. so that’s why it should be rejected.

Answer

You need to check in the HAVING clause if there is a row for that Supplier which satisfies the condition:

select Sno, avg(qty) avg_qty                                              
from Supplier
group by  sno
having count(case when qty > 50 and qty < 100 then 1 end) > 0

See the demo.
Results:

> Sno | avg_qty
> :-- | ------:
> S1  |      50
> S3  |      83
> S4  |      40