MySQL SUM: Examples

Examples
Sample Database

#1

SELECT SUM(quantityOrdered * priceEach) total
FROM orderdetails
WHERE orderNumber = 10100;

#2

SELECT orderNumber,
         SUM(quantityOrdered * priceEach) total
FROM orderdetails
GROUP BY  orderNumber
ORDER BY  SUM(quantityOrdered * priceEach) DESC;

#3

SELECT orderNumber,
         SUM(quantityOrdered * priceEach)
FROM orderdetails
GROUP BY  orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000
ORDER BY  SUM(quantityOrdered * priceEach);

#4

SELECT SUM(buyprice)
FROM products
ORDER BY buyprice DESC
LIMIT 10;

#5

SELECT SUM(buyprice) FROM
(SELECT buyprice
FROM products
ORDER BY buyprice DESC
LIMIT 10) price;

#6

SELECT COALESCE(SUM(quantityOrdered * priceEach),0)
FROM orderdetails
WHERE productCode = 'S1_20';

#7

SELECT SUM(quantityOrdered * priceEach) loss
FROM orderdetails
INNER JOIN orders USING(orderNumber)
WHERE status = 'Cancelled'

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result