Functions

Edit

Coalesce

The COALESCE() function returns the first non-null value in a list.

Syntax: COALESCE(val1, val2, ...., val_n)

Technical Details

Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
  • If we want to substitute a value from another column to use for the missing value in a different column, then we would use COALESCE.
  • Let’s say we want to look at product name, but if that data is missing we want it to substitute product code for the product name, and we’ll put the new data in a new column
SELECT
     COALESCE(product,product_code)AS product_info
FROM
    MyTable
  • If you remember the example in Merge: what we are asking is, if the value of warehouse is NULL then substitute it with ‘All warehouses’
  • As you see in the image that’s what happened in the total row
SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse

  • Here is another example
SELECT  COALESCE(NULL, 'Pick Me', 'No Pick ME') AS answer

[OUTPUT] Pick Me

Reduce

Trim

The TRIM() function removes the space character OR other specified characters from the start or end of a string.

By default, the TRIM() function removes leading and trailing spaces from a string.

Syntax

TRIM([characters FROM ]string)

Parameter Values

Parameter Description
characters FROM Optional. Specific characters to remove
string Required. The string to remove spaces or characters from 
SELECT   
        TRIM('   SQL   Tutorial ')  AS trimmed_string
FROM
WHERE 
        TRIM(order_state) = "PA"
  • Specify the characters to be trimmed: [#! ] being # and ! and < space >
SELECT TRIM('#! ' FROM '    #SQL Tutorial!    ') AS TrimmedString;

Ltrim & Rtrim

The LTRIM() function removes leading spaces from a string.

The RTRIM() function removes trailing spaces from a string.

Measure & Aggregate

Min & Max

What’s convenient with SQL is you can query the data using calculations and criteria, instead of extracting the data and then manipulating it, you can start data manipulation prior to connecting to the database.

  • Here for example we can extract the Min and Max values of a column and set them to an ALIAS right at the start
SELECT 
        MIN (used_date) AS min_date,
        MAX (used_date) AS max_date
FROM    
        TableName;

Avg

  • Calculates the AVG of the argument
SELECT    AVG (seasonal_sales) AS avg_sales_sea

Avg/Per

  • Let’s say we want to calculate the average cost per paperback book sold
  • So we will need the total cost/ total units(books)
  • So it’s a combination of two functions
SELECT  AVG(cost/quantity)
FROM    Books_table
WHERE   book_type = "paperback"

Sum

  • Calculates the SUM of the argument
SELECT    SUM (seasonal_sales) AS sum_sales_sea 

# ---- OR with WHERE ----
WHERE   SUM(seasonal_sales) >= projections_sales

Count

  • Sums up the occurrence of the argument, or counts the number of rows that matches a specified criterion
#_____ gives us the total number of products in the table
SELECT   COUNT(*) AS   inventory_count       
FROM     ProductTable


SELECT   COUNT(order_states) AS num_states


SELECT   COUNT(games = "INDOOR") AS indoor_games

# ---- OR WITH WHERE -----
SELECT   COUNT(games) AS indoor_games
WHERE    games = "INDOOR"

# --- ANOTHER EXAMPLE WITH 2 CONDITIONALS
SELECT   COUNT(games = "INDOOR") > MIN(required_games) AS min_indoor_games

#---- OR with WHERE ------
SELECT   games AS min_indoor_games
WHERE    COUNT(games = "INDOOR") > MIN(required_games)

Count Distinct

The COUNT function returns the number of records that are returned by a query that summed the occurrence of a variable. COUNT DISTINCT performs the same function but removes both duplicate rows of the same data and null values from the result set.

#___ Here we don't want to count the number of orders but
#___ the distinct number of accounts that placed an order
SELECT
     COUNT(DISTINCT order_id) AS num_orders

Convert

Cast

  • Convert any data type to another
  • Syntax: CAST(expression AS datatype(length))
expression Required. The value to convert
datatype Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length) Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary)
Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse 
#___ change price to a floating number, ride to a string of variable length
SELECT 
        CAST (purchase_price AS FLOAT64)
        CAST (ride AS VARCHAR)

#___ change order_date from datetime to date but filter out specific date range        
WHERE
        CAST (order_date AS DATE) BETWEEN '2020-12-01' AND '2020-12-31'
        
#___ change and sort price as a floating numeric
ORDER BY  
        CAST (purchase_price AS FLOAT64)  DESC

Cast - BigQuery Specific

In BigQuery SQL any alteration using ALTER TABLE is  stored into a temporary table, and if  you change the  destination location you are given an DLL error so the only other option is to use CAST, as seen below.

IMPORTANT: you have to select all columns in the table including the column you want  to change data type. If you just choose the one column and set the destination of the query to override the existing table it will create a new table with JUST ONE COLUMN the column you CAST upon.

So always SELECT * and then CAST certain column(s)

SELECT 
       *,
       CAST(ride_id AS VARCHAR)
FROM 
       TableName

Safe_cast

Using the CAST function in a query that fails returns an error in BigQuery. To avoid errors in the event of a failed query, use the SAFE_CAST function instead. The SAFE_CAST function returns a value of Null instead of an error when a query fails.

The syntax for SAFE_CAST is the same as for CAST. Simply substitute the function directly in your queries. The following SAFE_CAST statement returns a string from a date.

SELECT   SAFE_CAST(MyDate AS STRING)
FROM     MyTable

Scalar

Scalars perform operations on every input value

Round

ROUND(number, decimals, operation)

Parameter Description
number Required. The number to be rounded
decimals Required. The number of decimal places to round number to
operation Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it truncates the result to the number of decimals. Default value is 0

Rounds the first argument to the # of decimals specified

SELECT  ROUND(235.415, 2) AS round_value
[OUTPUT]  235.420

#___ any number other than 0 will truncate the output
SELECT  ROUND(235.415, 2,1)  AS round_operate
[OUTPUT]  235.410

Round Col Values

  • Of course we can round values before we filter them
  • Let’s say we want to round the cost column in a table
SELECT  ROUND(cost)
FROM    Petrescue_table

Length

Returns the length of a string (in bytes).

  • Let’s say a field is designed to be of 5 character length, but it appears that some were more so we can search for the fields that exceeded expectations by checking the LENGTH of the field
  • They work on string type values like VAR and VARCHAR
SELECT  customer_name
FROM    Customers
WHERE   LENGTH (customer_name) > 5

Ucase & Lcase

  • We can convert strings to U or L case values
SELECT  UCASE(animal_type)
FROM    Petrescue_table
  • Scalar can be used in a WHERE clause
  • In the event that we don’t know if the values are upper, lower or a mixture of the two we should convert the entire column first then use the WHERE clause on it
SELECT  *
FROM    Petrescue_table
WHERE   LCASE(animal_type) = 'dog'

Chain Together

  • We can have a scalar inside of another function
  • If we want to know the unique type of animals in the dataset we can do this
SELECT  DISTINCT(UCASE(animal_type))
FROM    Petrescue_table

Summary

This is a summary table of 4 sections, so it’ll be at the end of each of these sections:

  • Functions
  • Subqueries
  • Implicit Joins
  • DateTime
Command Syntax (MySQL/DB2) Description Example (MySQL/DB2)
COUNT SELECT COUNT(column_name) FROM table_name WHERE condition; COUNT function returns the number of rows that match a specified criterion. SELECT COUNT(dep_id) FROM employees;
AVG SELECT AVG(column_name) FROM table_name WHERE condition; AVG function returns the average value of a numeric column. SELECT AVG(salary) FROM employees;
SUM SELECT SUM(column_name) FROM table_name WHERE condition; SUM function returns the total sum of a numeric column. SELECT SUM(salary) FROM employees;
MIN SELECT MIN(column_name) FROM table_name WHERE condition; MIN function returns the smallest value of the SELECTED column. SELECT MIN(salary) FROM employees;
MAX SELECT MAX(column_name) FROM table_name WHERE condition; MAX function returns the largest value of the SELECTED column. SELECT MAX(salary) FROM employees;
ROUND SELECT ROUND(2number, decimals, operation) AS RoundValue; ROUND function rounds a number to a specified number of decimal places. SELECT ROUND(salary) FROM employees;
LENGTH SELECT LENGTH(column_name) FROM table; LENGTH function returns the length of a string (in bytes). SELECT LENGTH(f_name) FROM employees;
UCASE SELECT UCASE(column_name) FROM table; UCASE function displays the column name in each table in uppercase. SELECT UCASE(f_name) FROM employees;
LCASE SELECT LCASE(column_name) FROM table; LCASE function displays the column name in each table in lowercase. SELECT LCASE(f_name) FROM employees;
DISTINCT SELECT DISTINCT column_name FROM table; DISTINCT function is used to display data without duplicates. SELECT DISTINCT UCASE(f_name) FROM employees;
DAY SELECT DAY(column_name) FROM table DAY function returns the day of the month for a given date. SELECT DAY(b_date) FROM employees where emp_id = 'E1002';
CURRENT_DATE SELECT CURRENT_DATE; CURRENT_DATE is used to display the current date. SELECT CURRENT_DATE;
DATEDIFF() SELECT DATEDIFF(date1, date2); DATEDIFF() is used to calculate the difference between two dates or time stamps. The default value generated is the difference in number of days. SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;
FROM_DAYS() SELECT FROM_DAYS(number_of_days); FROM_DAYS() is used to convert a given number of days to YYYY-MM-DD format. SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;
DATE_ADD() SELECT DATE_ADD(date, INTERVAL n type); DATE_ADD() is used to calculate the date after lapse of mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days after what is mentioned in date column. The type valiable can also be months or years. SELECT DATE_ADD(date, INTERVAL 3 DAY);;
DATE_SUB() SELECT DATE_SUB(date, INTERVAL n type); DATE_SUB() is used to calculate the date prior to the record date by mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days before what is mentioned in date column. The type valiable can also be months or years. SELECT DATE_SUB(date, INTERVAL 3 DAY);;
Subquery SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

Subquery is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT emp_id, f_name, l_name, salaryFROM employeeswhere salary< (SELECT AVG(salary)FROM employees);

SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all;


SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs);
Implicit Inner Join SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; Implicit Inner Join combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns. SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;
Implicit Cross Join SELECT column_name(s) FROM table1, table2; Implicit Cross Join is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table. SELECT * FROM employees, jobs;