> For the complete documentation index, see [llms.txt](https://dev117uday.gitbook.io/databases/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dev117uday.gitbook.io/databases/sql/tables/coalesce.md).

# Usefull Functions

## Case

```sql
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.

```sql
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`

```sql
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`](https://www.postgresqltutorial.com/postgresql-group-by/) clause of the [`SELECT`](https://www.postgresqltutorial.com/postgresql-select/) statement.
* Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze and [aggregation function](https://www.postgresqltutorial.com/postgresql-aggregate-functions/) expressions.
* Third, in the `GROUP BY` clause, specify the dimension columns within the parentheses of the `CUBE` subclause.

```sql
select age_certificate, movie_length
from movies
group by age_certificate, cube (age_certificate, movie_length )
order by age_certificate;

```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/tables/coalesce.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.