Selecting productid from a table that has at least 3 feedbacks greater than 3-CodePudding

I have a table with three columns (id, productID, and feedback) containing feedbacks:

id | productID     | feedback
.............................
1  | 1000          | 5
2  | 1000          | 4
3  | 1000          | 3
4  | 1001          | 5
5  | 1002          | 5
6  | 1002          | 5
7  | 1003          | 4
8  | 1003          | 5
9  | 1003          | 5

I would like to select a productID that has minimum 3 feedbacks and none of them has a value less than 4. In this case, 1003 is the one that I would like to get.

CodePudding user response：

select t.prodid,count(t.prodid) from table as t 
where not exists 
(select 1 from table as t2 where t2.prodid=t.prodid and t2.feedback<4) 
group by t.prodid 
having count(*)>=3

CodePudding user response：

Using aggregation:

SELECT productID
FROM yourTable
GROUP BY productID
HAVING SUM(feedback > 3) >= 3;

CodePudding user response：

I think you want to use a MIN here to meet the requirement.

SELECT 
   productID
FROM 
   tableName
GROUP BY 
   productID
HAVING 
   MIN(feedback) >= 3;