SUM()

The SUM() function returns the sum of rows from a column selected in the query.Let motivate this with an example.

The query below returns a subset of the products table inlinecode>category, price, cost, stock_quantity

SELECT category, price, cost, stock_quantity 
FROM products
LIMIT 20; 

OUTPUTcategory | price | cost | stock_quantity ------------+---------+---------+---------------- Smartphones | 799.99 | 520.00 | 38 Laptops | 2499.99 | 1800.00 | 12 Laptops | 1299.99 | 900.00 | 23 Tablets | 599.99 | 400.00 | 67 Audio | 249.99 | 150.00 | 89 Audio | 349.99 | 200.00 | 34 Gaming | 299.99 | 180.00 | 156 Gaming | 499.99 | 350.00 | 8 Gaming | 499.99 | 350.00 | 15 Wearables | 399.99 | 250.00 | 78 (10 rows)

SUM()

Suppose we want to compute the potential profit we could make for Laptops. We can do this by taking the difference between cost and price, multiply it by stock_quantity and adding the values.

SELECT sum((price - cost) * stock_quantity) As expected_profit
FROM products;

OUTPUT expected_profit ----------------- 354828.41 (1 row)

AVG()

The AVG() function returns the numeric average of the number of rows selected in the query. In the example below, we compute average profit per unit

SELECT avg((price - cost) * stock_quantity) As expected_avg_profit
    FROM products;

OUTPUT expected_avg_profit ----------------------- 6957.4198039215686275 (1 row)