5 useful SQL functions to take your SQL skills to the next level
Tags: sql, beginners, programming, tutorial
Published: 2023-10-16 18:43:55

<p data-selectable-paragraph="">
  Hi Community,
</p>

<p data-selectable-paragraph="" id="b723">
  <strong>In this article, I listed 5 useful SQL functions with explanations and query examples </strong>👇🏻<br /><br />These 5 functions are
</p>

<li data-selectable-paragraph="">
  COALESCE
</li>
<li data-selectable-paragraph="">
  RANK
</li>
<li data-selectable-paragraph="">
  DENSE_RANK
</li>
<li data-selectable-paragraph="">
  ROW_NUMBER
</li>
<li data-selectable-paragraph="">
  Function to Get Running Totals
</li>

<p data-selectable-paragraph="">
  So Let us start with COALESCE function<!--break-->
</p>

<h1 data-selectable-paragraph="" id="b025">
  #COALESCE
</h1>

<p data-selectable-paragraph="" id="3756">
  The COALESCE function evaluates a list of expressions in left-to-right order and returns the value of the first non-NULL expression. If all expressions evaluate to NULL, NULL is returned.
</p>

<p data-selectable-paragraph="" id="12d3">
  Following statement will return first not null value which is 'intersystems'
</p>

<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">COALESCE</span>(<span class="hljs-literal">NULL</span>, <span class="hljs-literal">NULL</span>, <span class="hljs-literal">NULL</span>,<span class="hljs-string">'intersystems'</span>, <span class="hljs-literal">NULL</span>,<span class="hljs-string">'sql'</span>)</code></pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qm9h88vta5zzzm5f1vih.png)

<p data-selectable-paragraph="">
  Let us create below  table for further example
</p>

<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> EXPENSES(
    TDATE     <span class="hljs-built_in">DATE</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,
    EXPENSE1   <span class="hljs-built_in">NUMBER</span> <span class="hljs-literal">NULL</span>,
    EXPENSE2   <span class="hljs-built_in">NUMBER</span> <span class="hljs-literal">NULL</span>,
    EXPENSE3   <span class="hljs-built_in">NUMBER</span> <span class="hljs-literal">NULL</span>,
    TTYPE  <span class="hljs-built_in">CHAR</span>(<span class="hljs-number">30</span>) <span class="hljs-literal">NULL</span>)</code></pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7auokktxxivc1fovy1hw.png)

<p data-selectable-paragraph="">
  Now let us insert some dummy data to test our function
</p>

<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> sqluser.expenses (tdate, expense1,expense2,expense3,ttype )  
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-01'</span>}, <span class="hljs-number">500</span>,<span class="hljs-number">400</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Present'</span>
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-01'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-number">50</span>,<span class="hljs-number">30</span>,<span class="hljs-string">'SuperMarket'</span>
  <span class="hljs-keyword">UNION</span> ALL 
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-01'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">30</span>,<span class="hljs-string">'Clothes'</span> 
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-02'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-number">50</span>,<span class="hljs-number">30</span> ,<span class="hljs-string">'Present'</span>
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-02'</span>}, <span class="hljs-number">300</span>,<span class="hljs-number">500</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'SuperMarket'</span>
  <span class="hljs-keyword">UNION</span> ALL 
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-02'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-number">400</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Clothes'</span>   
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-03'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">350</span> ,<span class="hljs-string">'Present'</span>
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-03'</span>}, <span class="hljs-number">500</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'SuperMarket'</span>
  <span class="hljs-keyword">UNION</span> ALL 
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-04'</span>}, <span class="hljs-number">200</span>,<span class="hljs-number">100</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Clothes'</span>
  <span class="hljs-keyword">UNION</span> ALL
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-06'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">100</span>,<span class="hljs-string">'SuperMarket'</span>
  <span class="hljs-keyword">UNION</span> ALL 
  <span class="hljs-keyword">SELECT</span> {d<span class="hljs-string">'2023-01-06'</span>}, <span class="hljs-literal">NULL</span>,<span class="hljs-number">100</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Clothes'</span>  </code></pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5wu1wq0y5sbzplok11tf.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r3snuqa26mn3pqqh1g8t.png)

<p data-selectable-paragraph="">
  Now by using COALESCE function we will retrieve first not NULL value from expense1,expense2 and expense 3 columns
</p>

<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">SELECT</span> TDATE,
<span class="hljs-keyword">COALESCE</span>(EXPENSE1,EXPENSE2,EXPENSE3),
TTYPE
<span class="hljs-keyword">FROM</span> sqluser.expenses <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-number">2</span>   </code></pre>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaib0wy9to73rq2duulk.png)

<h1 data-selectable-paragraph="" id="e602">
  #RANK vs DENSE_RANK vs ROW_NUMBER functions
</h1>

<ul id="RSQL_C148538">
  <li id="RSQL_C148533">
    RANK()— assigns a ranking integer to each row within the same window frame, starting with 1. Ranking integers can include duplicate values if multiple rows contain the same value for the window function field.
  </li>
  <li id="RSQL_C148535">
    ROW_NUMBER() — assigns a unique sequential integer to each row within the same window frame, starting with 1. If multiple rows contain the same value for the window function field, each row is assigned a unique sequential integer.
  </li>
  <li>
    DENSE_RANK() leaves<strong> </strong>no gaps after a duplicate rank.
  </li>
</ul>

<p data-selectable-paragraph="" id="1a96">
  In SQL, there’s several ways that you can assign a rank to a row, which we’ll dive into with an example. Consider once again the same example as before, but now we want to know what is the highest expenses.
</p>

<p data-selectable-paragraph="" id="e708">
  We want to know where do I spend the most money. There are different ways to do it. We can use all <code>ROW_NUMBER()</code> , <code>RANK()</code> and <code>DENSE_RANK()</code> . We will order the previous table using all three functions and see what are the main differences between them using the following query:
</p>

Below is our query:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7nijhnnpwk9nfu2o8u8r.png)

<p data-selectable-paragraph="" id="24dc">
  The main difference between al three functions is the way they deal with ties. We will further deep-dive their differences:
</p>

<li data-selectable-paragraph="" id="150b">
  <code>ROW_NUMBER()</code>returns a unique number for each row starting at 1. When there are ties, it arbitrarily assigns a number if a second criteria is not defined.
</li>
<li data-selectable-paragraph="" id="d980">
  <code>RANK()</code>returns a unique number for each row starting at 1, except for when there are ties, then it will assign the same number. As well, a gap will follow a duplicate rank.
</li>
<li data-selectable-paragraph="" id="24e6">
  <code>DENSE_RANK()</code><strong> </strong>leaves<strong> </strong>no gaps after a duplicate rank.
</li>

<h1 data-selectable-paragraph="">
  <!--break-->#Calculating Running Totals
</h1>

<p data-selectable-paragraph="" id="558e">
  The running total is probably one of the most useful window functions especially when you want to visualize growth. Using a window function with <code>SUM()</code>, we can calculate a cumulative aggregation.
</p>

<p data-selectable-paragraph="" id="bda6">
  To do so, we just need to sum a variable using the aggregator <code>SUM()</code> but order this function using a TDATE column. 
</p>

<p data-selectable-paragraph="">
  You can observe the corresponding query as follows:
</p>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eef34e4bmns6tknchaj9.png)

As you can observe in the table above, now we have the accumulated aggregation of the amount of money spent as the date passes by.

 

<h1 data-selectable-paragraph="">
  <!--break-->Conclusion
</h1>

<p data-selectable-paragraph="" id="a094">
  SQL is great. Functions used above might be useful when dealing data analysis, data science, and any other data-related field.
</p>

<p data-selectable-paragraph="" id="a9e3">
  This is why you should care to keep improving your SQL skills.
</p>

<p data-selectable-paragraph="" id="1560">
  <br /><b>Thanks</b>
</p>