Top10 SQL Functions for Data Analysis

1. Aggregate Functions:

• COUNT():

• • • Syntax: COUNT(expression)
    • Description: Counts the number of rows in a result set based on the specified expression.

• SUM():

• • • Syntax:SUM(expression)
    • Description: Calculates the sum of values in a numeric column based on the specified expression.

• AVG():

• • • Syntax: AVG(expression)
    • Description:Computes the average of values in a numeric column based on the specified expression.

• MIN():

• • • Syntax: MIN(expression)
    • Description: Finds the minimum value in a column based on the specified expression.

• MAX():

• • Syntax: MAX(expression)
• Description: Finds the maximum value in a column based on the specified expression.

2. Mathematical Functions:

• • ROUND():
    • Syntax: ROUND(number, decimals)
    • Description: Rounds a numeric value to the specified number of decimal places.
  • CEIL() or CEILING():
    • Syntax: CEIL(number) or CEILING(number)
    • Description: Rounds a numeric value up to the nearest integer.
  • FLOOR():
    • Syntax: FLOOR(number)
    • Description: Rounds a numeric value down to the nearest integer.
  • ABS():
    • Syntax: ABS(number)
    • Description: Returns the absolute value of a numeric expression.
  • POWER():
    • Syntax: POWER(base, exponent)
    • Description: Raises a numeric value to the power of another.

3.String Functions:

• • • • CONCAT() or CONCATENATE():
        • Syntax: CONCAT(string1, string2, …)
        • Description: Concatenates two or more strings.
      • SUBSTRING() or SUBSTR():
        • Syntax: SUBSTRING(string, start, length) or SUBSTR(string, start)
        • Description: Extracts a substring from a string.
      • LENGTH() or LEN():
        • Syntax: LENGTH(string) or LEN(string)
        • Description: Returns the length of a string.
      • UPPER():
        • Syntax: UPPER(string)
        • Description: Converts a string to uppercase.
      • LOWER():
        • Syntax: LOWER(string)
        • Description: Converts a string to lowercase.
      • TRIM():
        • Syntax: TRIM([leading | trailing | both] characters FROM string)
        • Description: Removes leading and/or trailing spaces from a string.

4. Date and Time Functions:

• • • • • • NOW():
            • Syntax: NOW()
            • Description: Returns the current date and time.
          • DATE():
            • Syntax: DATE(datetime)
            • Description: Extracts the date portion from a datetime value.
          • TIME():
            • Syntax: TIME(datetime)
            • Description: Extracts the time portion from a datetime value.
          • DATEDIFF():
            • Syntax: DATEDIFF(interval, start_date, end_date)
            • Description: Calculates the difference between two dates.
          • DATEADD():
            • Syntax: DATEADD(interval, value, start_date)
            • Description: Adds a specified time interval to a date.

5. Logical Functions:

• • • • • • • • CASE WHEN():
                • Syntax: CASE
                •     WHEN condition1 THEN result1
                •     WHEN condition2 THEN result2
                •     …
                •     ELSE else_result
                • END
                • Description: Performs conditional logic within a query.
              • COALESCE():
                • Syntax: COALESCE(value1, value2, …)
                • Description: Returns the first non-null expression in a list

6. Window Functions:

• • ROW_NUMBER():
    • Syntax: ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column)
    • Description: Assigns a unique number to each row within a partition of a result set.
  • RANK():
    • Syntax: RANK() OVER (PARTITION BY partition_column ORDER BY order_column)
    • Description: Assigns a rank to each row based on the values in one or more columns.
  • DENSE_RANK():
    • Syntax: DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column)
    • Description: Similar to RANK(), but without gaps between rank values.
  • LEAD():
    • Syntax: LEAD(column, offset, default_value) OVER (ORDER BY order_column)
    • Description: Accesses data from subsequent rows within the result set.
  • LAG():
    • Syntax: LAG(column, offset, default_value) OVER (ORDER BY order_column)
    • Description: Accesses data from previous rows within the result set.

7. Grouping Functions:

• • GROUP BY():
    • Syntax: GROUP BY column1, column2, …
    • Description: Groups rows based on the values in specified columns.
  • HAVING():
    • Syntax: HAVING condition
    • Description: Filters group rows based on a specified condition.

 

8. Conversion Functions:

• CAST():
  • Syntax:CAST(expression AS data_type)
  • Description: Converts a value from one data type to another.
• CONVERT():
  • Syntax:CONVERT(data_type, expression, style)
  • Description: Converts a value from one data type to another, with an optional style parameter for date and time conversions.

9. Conditional Functions:

• IFNULL() or ISNULL():
  • Syntax:IFNULL(expression, value_if_null) orISNULL(expression, value_if_null)
  • Description: Returns the specified value if the expression is NULL.
• NULLIF():
  • Syntax:NULLIF(expression1, expression2)
  • Description: Returns NULL if the two expressions are equal; otherwise, returns the first expression.

10. Aggregation with DISTINCT:

• SUM(DISTINCT column):
  • Syntax:SUM(DISTINCT column)
  • Description: Calculates the sum of distinct values in a numeric column.
• AVG(DISTINCT column):
  • Syntax:AVG(DISTINCT column)
  • Description: Computes the average of distinct values in a numeric column.
• COUNT(DISTINCT column):
  • Syntax:COUNT(DISTINCT column)
  • Description: Counts the number of distinct values in a column.

11. String Pattern Matching:

• LIKE:
  • Syntax:column LIKE pattern
  • Description: Matches a column value against a specified pattern using wildcard characters (% and _).
• SUBSTRING() with POSITION():
  • Syntax:SUBSTRING(string FROM POSITION(substring IN string))
  • Description: Retrieves a substring starting from the position of another substring in the main string.
• REGEXP:
  • Syntax:column REGEXP pattern
  • Description: Matches a column value against a regular expression pattern.

12. Coordinated Universal Time (UTC) Functions:

• UTCNOW():
  • Syntax:UTCNOW()
  • Description: Returns the current date and time in Coordinated Universal Time (UTC).
• GETUTCDATE():
  • Syntax:GETUTCDATE()
  • Description: Returns the current date and time in Coordinated Universal Time (UTC).