📔
Databases
  • Table of contents
  • SQL
    • Getting Started
      • PgAdmin Tool
    • Data Types
      • Arrays
      • Date/Time/Stamps
      • JSON
      • Internal Functions
      • Sequences
      • User Defined Data Types
    • Database
      • Schema
    • Table
      • Aggregation
      • Usefull Functions
      • Combining Tables
      • Constraints
      • Common Table Expression
      • GROUP BY and HAVING
      • OPERATORS
      • ORDER BY and DISTINCT
      • Views
      • WHERE Clause
    • Order of SQL Execution
    • Advance Table
      • Internals
      • Managing Tables
      • Partitioning Tables
      • Pivotal or Crosstab Tables
    • Joins
      • Cross & Natural Joins
      • Full, Multiple & Self Joins
      • Inner Join
      • Left and Right JOIN
    • Functions
      • Cursors
      • PL/pgSQL
      • Stored Procedures
      • Triggers
        • More on Triggers
    • Indexing
      • Custom Indexes
      • GIN Index
      • Indexes
      • SQL
      • Unique Index
    • Summarization
      • SubQueries
      • Window
  • MongoDB
    • Mongo Administration
    • MongoDB Aggregation
    • MQL
  • Redis
  • Cassandra
    • CQL
    • Data Modelling
      • Advance Data Modelling
    • Cassandra Administration
    • Cassandra Features
Powered by GitBook
On this page
  • Case
  • Coalesce
  • NULLIF
  • Cube

Was this helpful?

  1. SQL
  2. Table

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

  • 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;
PreviousAggregationNextCombining Tables

Last updated 3 years ago

Was this helpful?

First, specify the CUBE subclause in the the clause of the statement.

Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze and expressions.

GROUP BY
SELECT
aggregation function