MySQL Aggregate Functions: Examples (original) (raw)

#1 SELECT AVG(buyPrice) average_buy_price FROM products; #2 SELECT productLine, AVG(buyPrice) FROM products GROUP BY productLine ORDER BY productLine; #3 SELECT COUNT(*) AS total FROM products #4 SELECT productLine, COUNT(*) FROM products GROUP BY productLine ORDER BY productLine; #5 SELECT productCode, SUM(priceEach * quantityOrdered) total FROM orderDetails GROUP BY productCode ORDER BY total DESC; #6 SELECT productCode, productName, SUM(priceEach * quantityOrdered) total FROM orderDetails INNER JOIN products USING (productCode) GROUP BY productCode ORDER BY total; #7 SELECT MAX(buyPrice) highest_price FROM products; #8 SELECT productLine, MAX(buyPrice) FROM products GROUP BY productLine ORDER BY MAX(buyPrice) DESC; #9 SELECT MIN(buyPrice) lowest_price FROM products; #10 SELECT productLine, MIN(buyPrice) FROM products GROUP BY productLine ORDER BY MIN(buyPrice); #11 SELECT firstName, lastName, GROUP_CONCAT( DISTINCT customername ORDER BY customerName) customers FROM employees INNER JOIN customers ON customers.salesRepEmployeeNumber = employeeNumber GROUP BY employeeNumber ORDER BY firstName , lastname;