Skip to content

Usefull Functions

Case

SELECT movie_id,
       movie_name,
       CASE
           WHEN movies.movie_length < 100
               AND movies.movie_length <= 50 THEN 'Short'
           WHEN movies.movie_length > 100
               AND movies.movie_length <= 130 THEN 'Medium'
           WHEN movies.movie_length > 130 THEN 'Long'
           END duration
FROM movies
ORDER BY movie_name;
LIMIT 10;

 movie_id |          movie_name           | duration 
----------+-------------------------------+----------
        1 | A Clockwork Orange            | Medium
        2 | Apocalypse Now                | Long
        3 | Battle Royale                 | Medium
        4 | Blade Runner                  | Medium
        5 | Chungking Express             | Medium
        6 | City of God                   | Long
        7 | City of Men                   | Long
        8 | Cold Fish                     | Medium
        9 | Crouching Tiger Hidden Dragon | Long
       10 | Eyes Wide Shut                | Medium
(10 rows)


SELECT
       SUM(CASE age_certificate
             WHEN '12' THEN 1 
             ELSE 0 
           END) "Kids",
       SUM(CASE age_certificate
             WHEN '15' THEN 1 
             ELSE 0 
           END) "School",
       SUM(CASE age_certificate
             WHEN '18' THEN 1 
             ELSE 0 
           END) "Teens",
       SUM(CASE age_certificate
             WHEN 'PG' THEN 1 
             ELSE 0 
           END) "Restricted",
       SUM(CASE age_certificate
             WHEN 'U' THEN 1 
             ELSE 0 
           END) "Universal"
FROM movies;

  Kids | School | Teens | Restricted | Universal 
------+--------+-------+------------+-----------
   11 |     16 |     8 |         12 |         6

Coalesce

COALESCE function that returns the first non-null argument.

SELECT
    COALESCE (NULL, 2 , 1);

NULLIF

The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1

SELECT
    NULLIF (1, 1); -- return NULL

SELECT
    NULLIF (1, 0); -- return 1

SELECT
    NULLIF ('A', 'B'); -- return A

Cube

  • First, specify the CUBE subclause in the the GROUP BY clause of the SELECT statement.
  • Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze and aggregation function expressions.
  • Third, in the GROUP BY clause, specify the dimension columns within the parentheses of the CUBE subclause.
select age_certificate, movie_length
from movies
group by age_certificate, cube (age_certificate, movie_length )
order by age_certificate;