Details
-
Type:
Improvement
-
Status: Resolved
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 1.9.9
-
Fix Version/s: 2.0.2
-
Component/s: Block: AJAX Marking
-
Labels:None
-
Affected Branches:MOODLE_19_STABLE
-
Fixed Branches:MOODLE_20_STABLE
Description
From http://www.dbapool.com/downloads/whitepapers/tipsforesql.pdf
FROM emp
WHERE sal < 2000;
SELECT COUNT
FROM emp
WHERE sal BETWEEN 2000 AND 4000;
SELECT COUNT
FROM emp
WHERE sal>4000;
However, it is more efficient to run the entire query in a single statement. Each number is
calculated as one column. The count uses a filter with the CASE statement to count only the
rows where the condition is valid. For example:
SELECT COUNT (CASE WHEN sal < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN sal BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM emp;