Apps Media

SQL MIN function Description The SQL MIN function is used to return the minimum value of an expression in a SELECT statement. Syntax The syntax for the SQL MIN function is: SELECT MIN( expression ) FROM tables WHERE conditions; Parameters or Arguments expression can be a numeric field or formula. Example - With Single expression The simplest way to use the SQL MIN function would be to return a single field that calculates the MIN value. For example, you might wish to know the minimum salary of all employees.   SELECT MIN(salary) AS "Lowest salary" FROM employees; In this SQL MIN function example, weve aliased the MIN(salary) field as "Lowest salary". As a result, "Lowest salary" will display as the field name when the result set is returned. Example - Using SQL GROUP BY In some cases, you will be required to use the SQL GROUP BY clause with the SQL MIN function. For example, you could also use the SQL MIN function to return the name of each d...

SQL Function for finding Number of Column in a Given Table Below a function which can be used to find the no.of column in a specified Table.This function returns an INT(Scalar Value).This Type of function is known as scalar valued function. --FUNCTION FOR FINDING NO OF COLUMN FROM GIVEN TABLE --Create a function with Table_Name as Parameter CREATE FUNCTION FUN_COL_COUNT(@T_NAME VARCHAR(50)) RETURNS INT AS BEGIN DECLARE @CNT INT SELECT @CNT=MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@T_NAME RETURN @CNT END --Run the Function SELECT DBO.FUN_COL_COUNT(Table_name) This above statement return the no.of column in the Table_name .Here DBO represents DataBase Object(Schema).More about Schema read some other articles/Posts. Here Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables...

SQL SUM function Description The SQL SUM function is used to return the sum of an expression in a SELECT statement. Syntax The syntax for the SQL SUM function is: SELECT SUM( expression ) FROM tables WHERE conditions; Parameters or Arguments expression can be a numeric field or formula. Example - With Single Expression For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year. SELECT SUM(salary) AS "Total Salary" FROM employees WHERE salary > 25000;   In this SQL SUM Function example, weve aliased the SUM(salary) expression as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned. Example - Using SQL DISTINCT You can use the SQL DISTINCT clause within the SQL SUM function. For example, the SQL SELECT statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year. SELECT SUM(DISTINCT sal...

SQL AVG function Description The SQL AVG function is used to return the average of an expression in a SELECT statement. Syntax The syntax for the SQL AVG function is: SELECT AVG( expression ) FROM tables WHERE conditions; Parameters or Arguments expression can be a numeric field or formula. Example - With Single Expression For example, you might wish to know how the average cost of all products that are in the Clothing category. SELECT AVG(cost) AS "Average Cost" FROM products WHERE category = Clothing;   In this SQL AVG Function example, weve aliased the AVG(cost) expression as "Average Cost". As a result, "Average Cost" will display as the field name when the result set is returned. Example - Using SQL DISTINCT You can use the SQL DISTINCT clause within the AVG function. For example, the SELECT statement below returns the combined average cost of unique cost values where the category is Clothing. SELECT AVG(DISTINCT cost) AS "Average Cost" F...

SQL COUNT function Description The SQL COUNT function is used to count the number of rows returned in a SELECT statement. Syntax The syntax for the SQL COUNT function is:   SELECT COUNT( expression ) FROM tables WHERE conditions; Parameters or Arguments expression can be a numeric field or formula. Only includes NOT NULL Values Not everyone realizes this, but the SQL COUNT function will only include the records in the count where the value of expression in COUNT( expression ) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations. Lets look at a SQL COUNT function example that demonstrates how NULL values are evaluated by the COUNT function. For example, if you have the following table called suppliers : supplier_id supplier_name state 1 IBM CA 2 Microsoft 3 NVIDIA  And if you ran the following SQL SELECT statement that uses the SQL COUNT function:   SELECT COUNT(supplier_id) FROM suppliers; This SQL COUNT ex...

SQL MAX function Description The SQL MAX function is used to return the maximum value of an expression in a SELECT statement. Syntax The syntax for the SQL MAX function is: SELECT MAX( expression ) FROM tables WHERE conditions; Parameters or Arguments expression can be a numeric field or formula. Example - With Single expression The simplest way to use the SQL MAX function would be to return a single field that calculates the MAX value. For example, you might wish to know the maximum salary of all employees. SELECT MAX(salary) AS "Highest salary" FROM employees; In this SQL MAX function example, weve aliased the MAX(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned. Example - Using SQL GROUP BY Clause In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function. For example, you could also use the SQL MAX function to return the name of ea...