SQL JOIN + COUNT查询

Question

我试图显示一个表格显示课程列表。我想列出一个栏目，显示已完成课程的人数，如果可能的话，还包括尚未完成课程的人数。

我的表结构如下所示：

Courses 
courseid | title | etc 

Studying 
courseid | studentid | some other fields | has_completed 

理想的情况下，输出应该是这样的，例如：

Course Title | some other fields | Students completed | Students not completed 
Something  etc     5     3 

我有困难得到统计的两个独立的领域。任何人都可以推荐一个好方法来做到这一点？

任何意见赞赏，谢谢。

Answer 1

像这样的东西应该做的：

SELECT c.CourseID, c.Title, SUM(s.Has_Completed) Completed, SUM(1-s.Has_Completed) NotCompleted 
FROM Courses c 
LEFT JOIN Studying s ON c.CourseID = s.CourseID 
GROUP BY c.CourseID, c.Title 

Answer 2

尝试使用SQL count command

SELECT COUNT(has_completed) FROM Studying WHERE has_completed='1' 

Answer 3

如果HAS_COMPLETED的值为1为假的真和0，就可以解决这个问题很简单：

SELECT c.title, SUM(s.has_completed) as completed, COUNT(studentid) - SUM(s.has_completed) as notCompleted 
FROM Courses c, Studying s 
where c.courseid = s.courseid 
GROUP BY c.courseid, c.title