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.
Advertisement
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