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; 

category   |  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;

 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;

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