SELECT WHERE COUNT = 1

Question

Table LESSON has fields LessonDate, MemberId (Among others but only these two are relevant)

In English: Give me a list of the dates on which students that have only ever taken 1 class, took that class.

I have tried a number of things. Here's my latest attempt:

SELECT LessonDate 
FROM LESSON 
WHERE (SELECT COUNT(MemberId) GROUP BY (MemberId)) = 1

Just keeps returning SQL errors. Obviously, the syntax and logic are off.

Answer 1

The query may seem a little counter-intuitive at first. You need to group by MemberId to get the students who only took one class:

select max(l.LessonDate) as LessonDate
from Lesson l
group by l.MemberId
having count(*) = 1;

Because there is only one class for the student, max(l.LessonDate) is that date. Hence this does what you want: it gets all the dates for members who only took one class.

Your line of thinking is also pretty close. Here is the query that I think you were looking for:

SELECT LessonDate 
FROM LESSON 
WHERE MemberId in (SELECT l2.MemberId
                   FROM Lesson l2
                   GROUP BY l2.MemberId
                   HAVING COUNT(*) = 1
                  );

This approach is more generalizable, if you want to get the dates for members that have 2 or 3 rows.

Answer 2

You need to look into SELECT ... FROM ... GROUP BY ... HAVING There is a lot of documentation available online by searching GROUP BY e.g. This article

The following SQL groups by MemberId which I thnk is wrong as you want to count the number of member id's,

SELECT
    LessonDate
    , MemberId 
    , COUNT(*) AS Lesson_Count
FROM 
    Lesson 
GROUP BY
    LessonDate
    , MemberId 
HAVING
    COUNT(*) = 1 

The above query will give you a list of "dates", "members", and the number of lessons taken on that date by that "member". I don't think you need to have the aggregate function (COUNT(*) AS Lesson_Count) as part of the select statement but it's often "nice to have" to give you the confidence that your results are as you would expect.

Your query is actually failing because the subquery doesn't have a FROM statement but the above is better practice:

SELECT LessonDate 
FROM LESSON 
WHERE (SELECT COUNT(MemberId) FROM Lesson GROUP BY (MemberId)) = 1