Tip on using the CASE expression.

Tip on using the CASE expression.

Rating:

You must use the CASE expression if many calculations are being performed by using multiple queries on same row of a table. The following example shows how you should use the CASE expression rather than using the multiple queries:

SELECT COUNT (*)
   FROM students
   WHERE mark<50;

SELECT COUNT(*)
   FROM students
   WHERE mark BETWEEN 50 AND 75;

SELECT COUNT(*)
   students
   WHERE mark>75

Rather than using the above individual queries, you can perform the above operation in a single query by using the CASE expression as given below:

SELECT
   COUNT(CASE WHEN mark<50 THEN 1 ELSE null END) fail,
   COUNT(CASE WHEN mark BETWEEN 50 AND 75 THEN 1 ELSE null END) fdiv,
   COUNT(CASE WHEN mark> THEN 1 ELSE null END) distinction
   FROM students;

Rating: