如何计算一个单元格中的数字与另一个表中列的 SUM 之间的差异

Question

我有不同课程的数据库。我的目标是计算任何课程的免费名额。我在一个表格中有一个属性，它显示了有多少人可以上课。在另一个表格中，我有关于学生的信息，在那里我可以看到学生正在学习哪门课程。所以，也许我应该计算所有地方的琥珀之间的差异，并计算课程中的学生数量。

我通过以下方式进行了查询：

SELECT (classes.amount_of_students - (SELECT COUNT(id_class) 
FROM classes_students 
WHERE id_class = 1)) AS free_places 
FROM classes 
INNER JOIN classes_students ON classes.id_class = classes_students.id_class;

但不幸的是，我有以下错误：

ERROR:  operator does not exist: character varying - bigint
LINE 1: SELECT (classes.amount_of_students - (SELECT COUNT(id_class)...
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

那么，问题是什么，我该如何解决呢？

UPD

classes_student
id_class | id_student 
----------+------------
1 |          1
2 |          2
1 |          3
students
id_student |  name  |  last_name   |      email      |   login   | password 
------------+--------+--------------+-----------------+-----------+----------
1 | Serhei | Mikhailovski | [e-mail]  | serzh     | 12345
2 | Bogdan | Zaitsev      | [e-mail] | bodyaga   | qwerty
3 | Vadim  | Yachin       | [e-mail]  | yanchilla | 13371488

CREATE TABLE classes_students (
id_class SERIAL REFERENCES classes (id_class),
id_student SERIAL REFERENCES student (id_student),
PRIMARY KEY (id_class, id_student)
);
CREATE TABLE classes (
id_class SERIAL PRIMARY KEY,
amount_of_hours VARCHAR NOT NULL,
amount_of_students VARCHAR NOT NULL,
id_subject SERIAL REFERENCES subject (id_subject),
id_teacher SERIAL REFERENCES teacher (id_teacher)
);  

Answer 1

正如@a_horse_with_no_name对您的帖子的评论，您正在尝试从该行的varchar中减去number：

(classes.amount_of_students - (SELECT COUNT(id_class) 

因为classes.amount_of_students属于VARCHAR型。
因此，您需要更改classes的表列amount_of_students并将其类型更改为int：

ALTER TABLE classes
ALTER COLUMN amount_of_students TYPE INT

或者将其cast以integer查询：

(nullif(classes.amount_of_students, '')::int - (SELECT COUNT(id_class)