Most Popular Tutorials




SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum

    • SQL Scalar functions

    SQL scalar functions return a single value, based on the input value.

  • UCASE() - Converts a field to upper case
  • LCASE() - Converts a field to lower case
  • MID() - Extract characters from a text field
  • LEN() - Returns the length of a text field
  • ROUND() - Rounds a numeric field to the number of decimals specified
  • NOW() - Returns the current system date and time
  • FORMAT() - Formats how a field is to be displayed

    • AVG() Function :-

    AVG() function returns the average value of a numeric column.

    SELECT AVG(column_name) FROM table_name;
    SELECT AVG(cost) AS Costaverage FROM Products;
    SELECT itemname, itemprice FROM Products WHERE cost>(SELECT AVG(cost) FROM Products);

    COUNT() Function :-

    COUNT() function returns the number of rows that matches a specified criteria.

    SELECT COUNT(column_name) FROM table_name;

    COUNT(*) Function :-

    COUNT(*) function returns the number of records in a table:

    SELECT COUNT(*) FROM table_name;

    COUNT(DISTINCT column_name) Function :-

    COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column.

    SELECT COUNT(DISTINCT column_name) FROM table_name;

    SELECT COUNT(productid) AS noof_products FROM Orders WHERE productid=3;

    limit keyword:-

    limit keyword gives no of row from table which you specify.

    SELECT name FROM Customers ORDER BY custoid ASC LIMIT 1;

    this query gives the top row of the table.

    SELECT CustomerName FROM Customers ORDER BY CustomerID DESC LIMIT 1;

    MAX() Function:-

    The MAX() function returns the highest value of the selected column.

    SELECT MAX(column_name) FROM table_name;

    SELECT MAX(salary) AS Highestsalary FROM customers;

    SELECT MAX(salary) FROM customers WHERE salary NOT IN (SELECT MAX(salary) FROM customers);
    select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee );

    select (SELECT MAX(salary) FROM customers) maxsalary, (SELECT MAX(salary) FROM customers WHERE salary NOT IN (SELECT MAX(salary) FROM customers )) as 2nd_max_salary

    MIN() Function:-

    The MIN() function returns the LOWEST value of the selected column.

    SELECT MIN(column_name) FROM table_name;

    HAVING Clause:-

    The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause. Notice that the HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.

    SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;

    GROUP BY Statement:-

    The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

    SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

    SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;

    Round Function.

    The ROUND() function is used to round a numeric field to the number of decimals specified.

    SELECT ROUND(column_name,decimals) FROM table_name;
    SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

    Between keyword.

    The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

    SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
    SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
    SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

    SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

    SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'M';

    SELECT * FROM Products WHERE ProductName NOT BETWEEN 'C' AND 'M';

    SQL Aliases :-

    SQL aliases are used to temporarily rename a table or a column heading. Basically aliases are created to make column names more readable

    SELECT column_name(s) FROM table_name AS alias_name;
    SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

    Count duplicate data in table.

    $qry = "SELECT salary, COUNT(*) as count
    FROM duplicate
    GROUP BY salary
    HAVING COUNT(*) > 1";

    CAST()

    The CAST() function is used to convert the type of an expression to a specified type. The function is similar to CONVERT() function.

    CAST(expr AS type))
    SELECT first_name, CAST(hire_date AS DATETIME) 'Joining Date' from employees where hire_date>('1987-09-15') ;
    SELECT enum_col FROM table_name ORDER BY CAST(enum_col AS CHAR);

    COALESCE Function

    COALESCE function returns the first non-NULL value from the given list. If there are no non-null values The COALESCE function returns NULL.

    we use COALESCE function with the City column. Instead of showing NULL, we are showing NA where the city column value is null.

    SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name, Gender, AGE, COALESCE(City, 'NA') AS City FROM Employee;

    we use COALESCE function with First, Middle, and Last Name column values.

    SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name, Gender, AGE, City FROM Employee;

    CONCAT function

    The CONCAT function can take n number of strings as arguments and return a single string as a result by adding all the input strings.

    CONCAT (string1, string2, string3, …)
    The CONCAT function take one or more arguments.
    It concatnate all arguments string and returns a single string as a result.
    The CONCAT function returns a binary string if the arguments include any binary strings.
    If the argument is numeric, it is converted to its equivalent nonbinary string form. Returns NULL if any argument is NULL.

    SELECT CONCAT(‘MySQL’, ‘ITTutorials’);
    // MySQLITtutorials
    SELECT CONCAT(‘MySQL’, ‘ ‘, ‘Tutorials’);

    Count()

    The COUNT() function is used to returns the number of records returned by a query. NULL values are not counted. It count all rows or only some rows of the table that matches a specified condition. it returns 0 if it does not find any matching rows. We can use the count function in three types :-

  • Count(*)
  • Count(expression)
  • Count(distinct)

    • COUNT(*) :- This function uses the SELECT statement to returns the count of rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.
    COUNT(expression) Function: This function returns the result set without containing Null rows as the result of an expression.
    COUNT(distinct expression) Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.

    Syntax :-

    SELECT COUNT (aggregate_expression) FROM table_name [WHERE conditions]; aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted. table_name: It specifies the tables from where you want to retrieve records. WHERE conditions: It specifies the conditions.

    Example

    SELECT COUNT(user_name) FROM Users;

    SELECT COUNT(*) FROM users WHERE age>18; SELECT COUNT(*) as total FROM users WHERE age>18;

    count distinct

    it counts the Non-Null and distinct rows in the column age:

    SELECT COUNT(DISTINCT age) FROM users;

    Count() with GROUP BY :-

    it returns the count of the element in each group.

    SELECT name, city, COUNT(*) FROM users GROUP BY city;

    Count() with HAVING and ORDER BY Clause

    the users name who has at least two age same and sorts them based on the count result:

    SELECT name, age, COUNT(*) as total FROM users GROUP BY age HAVING COUNT(*)>=2 ORDER BY COUNT(*);





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link

    Ittutorial