COUNT, SUM, AVG, MIN, MAX COUNT, SUM, AVG, MIN, MAX

1. Counting and adding

The shopkeeper does not want 22 rows — she wants one answer: “How much did I sell?” Aggregate functions squash many rows into one value:

• COUNT(*) — how many rows
• SUM(qty) — add up a column
• AVG(price) — the average
• MIN(price), MAX(price) — smallest and biggest

In the sales table each row is one bill line: which product_id, how many (qty), on which day, and the customer (NULL for walk-in buyers).

SELECT COUNT(*) AS bills, SUM(qty) AS units FROM sales;

                SELECT COUNT(*) AS items
FROM products;
              

Challenge: How much did we sell?

From sales, show one row with two columns: COUNT(*) named bills and SUM(qty) named units.

2. Cheapest, costliest, average

MIN, MAX and AVG work the same way. You can combine them with WHERE — the filter runs first, then the summary:

SELECT MAX(price) AS top FROM products WHERE category = 'Dairy';

AVG often gives long decimals; wrap it in ROUND(AVG(price), 2) for neat rupees and paise.

                SELECT MAX(price) AS top
FROM products
WHERE category = 'Dairy';
              

Challenge: Price range of the shop

From products, show one row: MIN(price) as cheapest, MAX(price) as costliest and ROUND(AVG(price), 2) as average.