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