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
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 theCUBE
subclause.
select age_certificate, movie_length
from movies
group by age_certificate, cube (age_certificate, movie_length )
order by age_certificate;
Last updated
Was this helpful?