在单个查询中从同一列不同的WHERE获取2个不同的结果

SELECT COUNT(NAME) AS NAMEA
FROM (DATA.1 WHERE MARKS > 50), COUNT(NAME) AS NAMEB FROM DATA.1

After running it I get this

运行后,我得到了这个

Syntax error in JOIN operation

JOIN操作中的语法错误

I am trying to get the percentage of student pass, no. of student pass, total no. of student and no. of student fail.

我想要获得学生准证的百分比,不。学生通行证,总数没有。学生和没有。学生失败了。

Please help me to find whats wrong in the above Query.

请帮我在上面的查询中找到什么错误。

please help me. Thanks

请帮帮我。谢谢

1 个解决方案

#1

You're essentially running two queries in one.

你实际上是在一个中运行两个查询。

Query 1:

SELECT COUNT(NAME) AS NAMEA
FROM DATA1
WHERE MARKS > 50

Query 2:

SELECT COUNT(NAME) AS NAMEB
FROM DATA1

If you want both columns in the same query then you would have to use a SUM of a CASE WHEN for the first query.

如果要在同一查询中同时使用两列,则必须对第一个查询使用CASE WHEN的SUM。

SELECT SUM(CASE WHEN MARKS > 50 THEN 1 ELSE 0 END) AS NAMEA,
       COUNT(NAME) AS NAMEB
FROM DATA1

For the rest of your points you would need the query:

对于其余的积分,您需要查询:

SELECT COUNT(NAME) AS TOTAL_STUDENTS,
       SUM(CASE WHEN MARKS > 50 THEN 1 ELSE 0 END) AS STUDENTS_PASSED,
       SUM(CASE WHEN MARKS > 50 THEN 1 ELSE 0 END)/COUNT(NAME) AS PASS_RATE,
       SUM(CASE WHEN MARKS < 50 THEN 1 ELSE 0 END) AS STUDENTS_FAILED
FROM DATA1

Bear in mind that this missed out students that have exactly 50 marks. If 50 is a pass then you would need to use >= 50 for STUDENTS_PASSED. If 50 is a fail then you would need to use <= 50 for STUDENTS_FAILED.

请记住,这错过了正好有50分的学生。如果50是一个传球,那么你需要对= STUDENTS_PASSED使用> = 50。如果50表示失败,那么您需要使用<= 50表示STUDENTS_FAILED。

#1