# Functions In SQL

## Introduction

1. Functions in SQL are divided into parts Built-in functions and User Defined functions
    
2. Built In functions can be further categorized as scalar, aggregate functions
    
3. Scalar Functions:
    
    * In SQL, a scalar function is a function that takes one or more input parameters and returns a single scalar value as the result.
        
    * Scalar functions are commonly used to perform calculations or manipulations on the input data and return a single value based on the provided parameters.
        
    * Scalar Function which will return a value for every single row that is passed in
        
    * Built-In scalar functions are round(), abs()
        
4. Aggregate Functions:
    
    * As the aggregation suggests they will perform aggregation or calculation on a set of values and will return a single value for each set of value
        
    * Aggregate functions in SQL are used to perform calculations on a set of values and return a single value as the result.
        
        Here are some common aggregate functions in SQL:
        
        1. `SUM()`: Calculates the sum of all values in a column.
            
            ```sql
            SELECT SUM(sales_amount) AS total_sales FROM sales;
            ```
            
        2. `AVG()`: Calculates the average value of a column.
            
            ```sql
            SELECT AVG(salary) AS average_salary FROM employees;
            ```
            
        3. `COUNT()`: Counts the number of rows or non-null values in a column.
            
            ```sql
            SELECT COUNT(*) AS total_rows FROM orders;
            ```
            
        4. `MIN()`: Returns the minimum value in a column.
            
            ```sql
            SELECT MIN(price) AS min_price FROM products;
            ```
            
        5. `MAX()`: Returns the maximum value in a column.
            
            ```sql
            SELECT MAX(quantity) AS max_quantity FROM inventory;
            ```
            
        6. `GROUP_CONCAT()`: Concatenates the values of a column within each group into a single string.
            
            ```sql
            SELECT department, GROUP_CONCAT(employee_name) AS employees_list FROM employees GROUP BY department;
            ```
            
        7. The `ORDER BY` clause in SQL is used to sort the result set of a query based on one or more columns in ***ascending (default)*** or ***descending*** order. It allows you to arrange the rows in a specific order before presenting the final output. The `ORDER BY` clause is often used in combination with the `SELECT` statement to control the sorting of the query results.
            
            The basic syntax of an `ORDER BY` query is as follows:
            
            ```sql
            sqlCopy codeSELECT column1, column2, ...
            FROM table_name
            ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
            ```
            
        8. `STDEV()`: Calculates the standard deviation of a column's values
            
            ```sql
            SELECT STDEV(scores) AS score_standard_deviation FROM exam_results;
            ```
            

## Count(column\_name) v/s Count(\*)

1. `COUNT(column_name)`:
    
    * This form of the `COUNT()` function counts the number of non-null values in the specified column.
        
    * It counts all rows where the specified column contains a non-null value.
        
    * If a row has a NULL value in the specified column, it is not included in the count.
        
    * You can use any column name within the parentheses to count the non-null values in that specific column.
        

Example:

```sql
SELECT COUNT(customer_id) AS non_null_customers FROM customers;
```

This will count the number of non-null `customer_id` values in the `customers` table.

1. `COUNT(*)`:
    
    * This form of the `COUNT()` function counts the total number of rows in the result set, regardless of whether there are NULL values in any column.
        
    * It counts all rows, including those that have NULL values in any column.
        
    * Using `COUNT(*)` is useful when you want to count the total number of rows in the result set, regardless of the individual column values.
        

Example:

```sql
SELECT COUNT(*) AS total_orders FROM orders;
```

This will count the total number of rows (orders) in the `orders` table, including any rows with NULL values in any column.

## Group By

The `GROUP BY` clause in SQL is used in combination with aggregate functions to group rows in a table based on the values in one or more columns. It allows you to perform aggregate calculations on groups of data, rather than on the entire dataset. The result of a `GROUP BY` query is typically a summary of the data grouped by specific criteria.

The basic syntax of a `GROUP BY` query is as follows:

```sql
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;
```

Let's break down the syntax:

* `SELECT`: Specifies the columns you want to include in the result set.
    
* `aggregate_function`: Represents any aggregate function like `COUNT`, `SUM`, `AVG`, `MAX`, `MIN`, etc., that you want to apply to the grouped data.
    
* `FROM`: Specifies the table from which you are retrieving the data.
    
* `GROUP BY`: Indicates the columns based on which the data should be grouped.
    

***When using*** `GROUP BY`***, the result set will have one row for each group, and the aggregate function will provide a summary value for each group.***

Example: Consider a table named `orders` with columns `order_id`, `customer_id`, `product_id`, and `order_amount`. If we want to find the total order amount for each customer, we can use `GROUP BY` as follows:

```sql
SELECT customer_id, SUM(order_amount) AS total_order_amount
FROM orders
GROUP BY customer_id;
```

***It's essential to note that when using*** `GROUP BY`***, the columns in the*** `SELECT` ***clause should either be part of the*** `GROUP BY` ***clause or be aggregate functions. Non-aggregated columns not included in the*** `GROUP BY` ***clause must be part of an aggregate function. Otherwise, the query will result in an error.***

## Having Clause

1. ***The*** `HAVING` ***clause in SQL is used to filter the results of a query based on the results of aggregate functions.*** It ***is similar to the*** `WHERE` ***clause, but while the*** `WHERE` ***clause filters individual rows before grouping and aggregating, the*** `HAVING` ***clause filters grouped data after the grouping and aggregation have taken place.***
    

The basic syntax of a query with the `HAVING` clause is as follows:

```sql
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2
HAVING condition;
```

Let's break down the syntax:

* `SELECT`: Specifies the columns you want to include in the result set, including any aggregate functions you want to use.
    
* `FROM`: Specifies the table from which you are retrieving the data.
    
* `GROUP BY`: Indicates the columns based on which the data should be grouped.
    
* `HAVING`: Introduces the filtering condition for the grouped data.
    
* `condition`: Represents the condition that must be met for a group to be included in the result set. This condition usually involves aggregate functions.
    

Example: Consider a table named `orders` with columns `order_id`, `customer_id`, and `order_amount`. If we want to find the total order amount for each customer and then filter only those customers with a total order amount greater than 1000, we can use the following query:

```sql
SELECT customer_id, SUM(order_amount) AS total_order_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;
```