Aggregate Functions in SQL Server

They take many rows and return ONE value.

COUNT -> how many
SUM   -> total
AVG   -> average
MIN   -> smallest
MAX   -> largest

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Sales;
GO

CREATE TABLE Sales
(
    SaleID   INT PRIMARY KEY,
    Product  VARCHAR(30),
    Category VARCHAR(30),
    City     VARCHAR(30),
    Quantity INT,
    Price    INT
);

INSERT INTO Sales VALUES
(1,  'Pen',      'Stationery', 'Hyderabad', 10, 10),
(2,  'Pencil',   'Stationery', 'Chennai',   20, 5),
(3,  'Notebook', 'Stationery', 'Hyderabad', 5,  50),
(4,  'Mouse',    'Electronics','Mumbai',    2,  500),
(5,  'Keyboard', 'Electronics','Hyderabad', 1,  1200),
(6,  'Pen',      'Stationery', 'Mumbai',    15, 10),
(7,  'Monitor',  'Electronics','Chennai',   1,  9000),
(8,  'Chair',    'Furniture',  'Hyderabad', 2,  3000),
(9,  'Table',    'Furniture',  'Mumbai',    1,  5000),
(10, 'Mouse',    'Electronics','Chennai',   3,  NULL);   -- price not known
GO

SELECT * FROM Sales;

(10 rows affected)

COUNT(*) - count all rows

SELECT COUNT(*) AS TotalSales FROM Sales;

COUNT(column) - counts only values that are NOT NULL

SELECT COUNT(Price) AS SalesWithPrice FROM Sales;       -- 9, because one Price is NULL

COUNT(DISTINCT column) - count unique values

SELECT COUNT(DISTINCT City) AS NumberOfCities FROM Sales;

SUM - total

SELECT SUM(Quantity) AS TotalQuantity FROM Sales;

AVG - average

SELECT AVG(Price) AS AveragePrice FROM Sales;            -- NULL is ignored

MIN and MAX

SELECT MIN(Price) AS LowestPrice, MAX(Price) AS HighestPrice FROM Sales;

All together in one query

SELECT COUNT(*)      AS TotalRows,
       SUM(Quantity) AS TotalQty,
       AVG(Price)    AS AvgPrice,
       MIN(Price)    AS MinPrice,
       MAX(Price)    AS MaxPrice
FROM Sales;

Aggregate with WHERE (only Hyderabad)

SELECT SUM(Quantity) AS HyderabadQuantity
FROM Sales
WHERE City = 'Hyderabad';

Aggregate on a calculation (total sales amount)

SELECT SUM(Quantity * Price) AS TotalAmount FROM Sales;

MIN and MAX also work on text (alphabetical order)

SELECT MIN(Product) AS FirstProduct, MAX(Product) AS LastProduct FROM Sales;

-- =============================================
-- Remember
--   * Aggregate functions ignore NULL values (except COUNT(*)).
--   * To get totals PER group (per city, per category) use GROUP BY
--     - see 09_GroupBy_Having_OrderBy.sql
-- =============================================