In this case, you can use the CASE expression to construct the query as follows: SELECT SUM ( CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 ENDįilm Code language: SQL (Structured Query Language) ( sql ) If the rental rate is 4.99, the film is premium.Īnd you want to know the number of films that belong to economy, mass, and premium.If the rental rate is 1.99, the film is mass.If the rental rate is 0.99, the film is economic.Suppose that you want to assign price segments to films with the following logic: B) Using CASE with an aggregate function example Note that we placed a column alias duration after the CASE expression. ORDER BY title Code language: PHP ( php ) WHEN length> 0 AND length 50 AND length 120 THEN 'Long' To apply this logic, you can use the CASE expression in the SELECT statement as follows: SELECT title, ![]() If the length is greater than 120 minutes, the film is long.If the length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium.If the lengh is less than 50 minutes, the film is short.Suppose you want to label the films by their length based on the following logic: Let’s take a look at the film table from the sample database. If you omit the ELSE clause, the CASE expression returns NULL. In case all conditions evaluate to false, the CASE expression returns the result ( else_result) that follows the ELSE keyword. Also, it immediately stops evaluating the next expression. For example, if the condition_2 evaluates to true, the CASE expression returns the result_2. If a condition evaluates to true, the CASE expression returns the corresponding result that follows the condition. When a condition evaluates to false, the CASE expression evaluates the next condition from the top to bottom until it finds a condition that evaluates to true. In this syntax, each condition ( condition_1, condition_2…) is a boolean expression that returns either true or false. The following illustrates the general form of the CASE statement: CASEĮND Code language: SQL (Structured Query Language) ( sql ) The CASE expression has two forms: general and simple form. Since CASE is an expression, you can use it in any places where an expression can be used e.g., SELECT, WHERE, GROUP BY, and HAVING clause. It allows you to add if-else logic to the query to form a powerful query. The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. The following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.Summary: in this tutorial, you will learn how to use the PostgreSQL CASE conditional expression to form conditional queries. ![]() ExampleĬonsider the COMPANY table, having the following records − For an action to be taken by the PostgreSQL statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE. You can combine N number of conditions using OR operator. The basic syntax of OR operator with WHERE clause is as follows − For example OR will be true if either condition1 or condition2 is true. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. The OR operator is also used to combine multiple conditions in a PostgreSQL statement's WHERE clause. The above given PostgreSQL statement will produce the following result − Testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000 The following SELECT statement lists down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00 − ExampleĬonsider the table COMPANY having records as follows − ![]() For an action to be taken by the PostgreSQL statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE. You can combine N number of conditions using AND operator. The basic syntax of AND operator with WHERE clause is as follows − ![]() For example AND will be true only when both condition1 and condition2 are true. While using AND operator, complete condition will be assumed true when all the conditions are true. The AND operator allows the existence of multiple conditions in a PostgreSQL statement's WHERE clause. These operators provide a means to make multiple comparisons with different operators in the same PostgreSQL statement. These two operators are called conjunctive operators. The PostgreSQL AND and OR operators are used to combine multiple conditions to narrow down selected data in a PostgreSQL statement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |