SQL select aggregate functions examples

John D K -

In this post we will cover most used SQL select aggregate functions and somoe special key words:

  • MIN(), MAX(), COUNT(), AVG() and SUM()
  • TOP, LIMIT or ROWNUM
  • SQL LIKE
  • SQL IN

SQL select MIN(), MAX(), COUNT(), AVG() and SUM()

Several very useful functions in SQL ( and MySQL ) could be used in order to get the maximum or mimimun value per column. The same can be done for average. If you don't provide where clause then the functions are applied over the whole table

SELECT 
	MAX(list_price) AS Maximum, 
	MIN(list_price) AS Minimal, 
	AVG(list_price) AS Average
FROM products; 

SELECT 
	SUM(quantity) AS sum
FROM purchase_order_details;

SQL select TOP, LIMIT or ROWNUM

In pure SQL you have 3 ways to limit the results:

  • TOP
  • LIMIT
  • ROWNUM
	SELECT TOP 3 * FROM customers;

	SELECT * FROM customers
	LIMIT 3;

	SELECT * FROM customers
	WHERE ROWNUM <= 3;

They are not implemented in MySQL except limit:

	SELECT * FROM customers
	LIMIT 3;

You can simulate rownum in MySQL by:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id, COUNT(*) as ordercount
  FROM orders
  where @rank < 5
  GROUP BY id
  ORDER BY ordercount DESC;

SQL select Like

You can search in table by using part of words:

  • Use a% - to search for first name starting with letter a
  • User _ - in order to search for any letter ( just one). So two underscores are searching for any 2 letters words. A_ is searching for two letter words starting with A
SELECT * FROM customers
	WHERE first_name LIKE 'a%'; 

SELECT * FROM customers
	WHERE first_name LIKE '____'
;

SQL in operator

You can use IN operator in order to use list as filter criteria to the output of the query:

SELECT * FROM customers
	WHERE job_title IN ('Owner', 'Purchasing Manager');

SELECT * FROM customers
	WHERE job_title NOT IN ('Owner', 'Purchasing Manager');