SQL Avg()

The AVG() returns the average value of a numeric column. Syntax:- SELECT AVG(column_name) FROM table_name; Example:- The subsequent SQL gets the average value of the “Price” column from the “Hosting” table:- SELECT AVG(Price) AS PriceAverage FROM Hosting; OR SELECT HostingName, Price FROM Hostings WHERE Price>(SELECT AVG(Price) FROM Hostings);

SQL Count()

The COUNT() returns the number of column or rows that matches a specified criteria. Syntax:- The COUNT(column_name) returns the number of values of the specified column:- SELECT COUNT(column_name) FROM table_name; OR SELECT COUNT(*) FROM table_name; OR SELECT COUNT(DISTINCT column_name) FROM table_name; Example:- The subsequent SQL counts the number of Visit from “VisitorID”=17 from the “Visitors” […]

SQL First()

The FIRST() function returns the first value of the selected column. Syntax:- SELECT FIRST(column_name) FROM table_name; OR SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1; Example:- The subsequent SQL selects the first value of the “VisitorName” column from the “Visitors” table:- SELECT FIRST(VisitorName) AS FirstVisitor FROM Visitors; OR SELECT VisitorName FROM Visitors ORDER […]

SQL Last()

The LAST() returns the last value of the selected column. Syntax:- SELECT LAST(column_name) FROM table_name; OR SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1; Example:- The subsequent SQL selects the last value of the “VisitorName” column from the “Visitors” table:- SELECT LAST(VisitorName) AS LastVisitor FROM Visitors; OR SELECT VisitorName FROM Visitors ORDER BY […]

SQL Group By

The GROUP BY is used in conjunction with the aggregate functions to group the result-set by columns. Syntax:- SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; Example:- If, we want to locate the number of visit. The subsequent SQL counts as Visit grouped by Visitors:- SELECT Visitors.VisitorName, COUNT(visits.visitID) AS NumberOfvisits FROM […]

SQL Having

The HAVING was added to SQL because the WHERE keyword cannot be used with aggregate functions. Syntax:- SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value; Example:- Now you want to find if any of the visitors has registered more than 10 orders. SELECT Visitors.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM […]