0

I have a task that needs to be solved using an SQL query in MS Acess: "In which dormitory students from not all groups live".
My table is called Dormitories and it has columns Student_name, Student_group, Dormitory_name.

I am trying to solve it like this:

SELECT Dormitory_name
FROM Dormitories
GROUP BY Dormitory_name
HAVING COUNT(DISTINCT Student_group) < (SELECT COUNT(DISTINCT Student_group) FROM Dormitories)

But I constantly get out "Syntax error (lack of operator)" in the last line. Explain what's wrong

user
  • 1
  • 2
  • 2
    Please tag the RDBMS you’re using. Answers can vary materially between each. – esqew Mar 06 '23 at 13:50
  • That's not what Having is for...you need that to be a where clause. – Neville Kuyt Mar 06 '23 at 13:53
  • I am doing this in MS Access – user Mar 06 '23 at 13:56
  • @NevilleKuyt If `having` is not for that, what is it for? – GSerg Mar 06 '23 at 13:59
  • [How to ask a good SQL question](https://meta.stackoverflow.com/a/271056/3820271). Sample data and expected result are crucial. – Andre Mar 06 '23 at 15:54
  • Where is to filter records based on a condition. Having is to filter rows based on an aggregate, e.g. "show me dormitories and the number of students in that dormitory, but only for dormitories with at least 3 students". – Neville Kuyt Mar 06 '23 at 16:54
  • @NevilleKuyt And like you said, the rows here are filtered based on an aggregate, namely, `count(distinct Student_group)`. How are you going to put that in a `where`? – GSerg Mar 06 '23 at 18:58
  • There is no aggregate in your select - that would be "select dormitory_name, count(*) from...having count(*) > (select count(*)...)" – Neville Kuyt Mar 07 '23 at 08:43
  • @NevilleKuyt That is not my `select`. How does the absence of an aggregate in the `select` portion of the query affect the presence of an aggregate in the `having` portion of the query? It is the fact that you are filtering on an aggregate that makes you use `having`, not the fact that you are selecting an aggregate. Can you demonstrate how you would put `COUNT(DISTINCT Student_group) < (SELECT COUNT(DISTINCT Student_group) FROM Dormitories)` in a `where`? – GSerg Mar 08 '23 at 08:50

0 Answers0