![]() ![]() department: the department where the subject is taught. ![]() number_of_lectures: the number of lectures throughout the year.I’ll use the table subject which has the following columns: This example will help you understand the concept better. Even though you’re probably used to using the SUM() function for summing values, it can also be used for counting. Let’s get serious now! A CASE WHEN expression is often used with a SUM() function in more complex reports, which can be quite challenging for beginners. Example 2: The CASE WHEN Expression With a SUM() and a GROUP BY If you want to delve more into the syntax, this very thorough article on the CASE WHEN logic could be quite helpful. Remember: when the rows don’t match any of the conditions defined, the CASE statement will return NULL values. There’s no “Poor result” category anymore. The code did run without an error, but the result is different. Run the code and examine its result: first_name Here’s the same code as the above but without the ELSE condition: Note that an ELSE condition is not mandatory in a CASE statement. I’m sure you want to see the result: first_name I’ve also ordered the results in descending order by the column score. After that, you can define the name of the column in which the results of your CASE statement will be stored in this case, it’s the column score_category. For example, if the score is more than 90, it is categorized as an “Exceptional result.” If it’s more than 70, it is a “Great result.” You don’t have to state “and less than 90” explicitly SQL takes other conditions into account to avoid duplicated results or errors.įollowing the same logic, every score above 50 is considered an “Average result.” Any score not satisfying any of the above three conditions is categorized as a “Poor result” remember, ELSE is used to assign the value when none of the conditions defined by CASE and WHEN are met.Ī CASE statement is closed by an END. After that, I define the conditions to be checked by the CASE statement and the values to be assigned to do that, I use WHEN and THEN. But then the fun part starts! The CASE statement starts with the keyword CASE, naturally. The SELECT statement selects the first and the last names of the students and their test scores from the table test_result nothing unusual here. WHEN score > 90 THEN 'Exceptional result' The task is to assign test result categories according to the score. last_name: the last name of the student.first_name: the first name of the student.We have a table named test_result which contains test scores. Let me show you the logic and the CASE WHEN syntax in an example. It runs a logical test in the case when the expression is true, then it will assign a specific value to it. The break statement tells PHP to break out of the switch-case statement block once it executes the code associated with the first true case.The structure of the CASE WHEN expression is the same. To prevent this add a break statement to the end of each case block. statement by statement) and once PHP finds a case statement that evaluates to true, it's not only executes the code corresponding to that case statement, but also executes all the subsequent case statements till the end of the switch block automatically. The switch statement executes line by line (i.e. The switch-case statement differs from the if-elseif-else statement in one important way.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |