Geek asks and answers

SQL - multiple lines in a HAVING clause

ok, so the request I would like to resolve is:

I would like to express the following business rule:

Display groups (for a particular tutorial (tutorialID) and week (s)) requiring additional reviews.

So, basically I need to determine the number of members in the group, then I need to collect all the hits for this week and this group and see if this amount is equal to the number of members in the group. If so, it means that all participants were viewed this week, and therefore they do not need to be displayed.

Assumptions: A member can only be viewed once a week.

I tried the following SQL, however I get the following error Subquery returns more than 1 row

SELECT groupName, g.groupId
FROM `student` s, `group` g
WHERE s.GroupId = g.GroupId 
AND s.GroupId IS NOT NULL
AND s.tutorialId =  2
GROUP by s.GroupId
AND s.GroupID = (
    SELECT GroupId
    FROM student
    GROUP BY GroupId
    HAVING  count(*)> (
        SELECT count(*)
        FROM scrumreview r, student s
        WHERE r.reviewee = s.studentID
        GROUP BY GroupId    
        AND r.week = 5
        )
    )

Student enter image description here

scrumreview enter asdasddescription here

groups enter image description here

+3
source share
3 answers

( 8) groupid s. = 1--1, 1--, IN.

, , 7 ...

AND s.GroupID = (

... :

AND s.GroupID IN (
+3
SELECT GroupId
FROM student
GROUP BY GroupId
HAVING  count(*)> (
    SELECT count(*)
    FROM scrumreview r, student s
    WHERE r.reviewee = s.studentID  
    AND r.week = 5
    )

1 , SQL, , :

AND s.GroupID = (

IN:

AND s.GroupID IN (
0

Try this instead:

SELECT groupName, 
g.groupId FROM `student` s, 
`group` g WHERE s.GroupId = g.GroupId  
AND s.GroupId IS NOT NULL 
AND s.tutorialId =  2 
GROUP by s.GroupId AND s.GroupID in (SELECT GroupId FROM student GROUP BY GroupId 
HAVING  count(*)> (SELECT count(*) FROM scrumreview r, student s WHERE r.reviewee =
s.studentID AND r.week = 5))
0
source

More articles:


All Articles