COUNT, SUM, AVG in SQL Server: Turning Rows Into Insight

Everything so far has returned individual rows. Aggregate functions are the first tool that collapses many rows into one summary value — the foundation of every report, dashboard, and “how many / how much / on average” question you’ll ever answer with SQL.

Setup — a small startup funding dataset to follow along:

CREATE TABLE dbo.Startup (
    startup_id    INT IDENTITY(1,1) PRIMARY KEY,
    name          NVARCHAR(100) NOT NULL,
    industry      NVARCHAR(50)  NOT NULL,
    founded_year  INT           NOT NULL,
    funding_usd   DECIMAL(15,2) NOT NULL,
    employees     INT           NOT NULL
);
INSERT INTO dbo.Startup (name, industry, founded_year, funding_usd, employees) VALUES
('DataForge', 'AI', 2019, 12000000, 45),
('CloudNest', 'Cloud Infra', 2018, 25000000, 120),
('PayFlow', 'Fintech', 2020, 8000000, 30);

The Five Core Aggregate Functions

SELECT COUNT(*) AS total_startups FROM dbo.Startup;
SELECT SUM(funding_usd) AS total_funding FROM dbo.Startup;
SELECT AVG(employees) AS avg_team_size FROM dbo.Startup;
SELECT MIN(founded_year) AS oldest, MAX(founded_year) AS newest FROM dbo.Startup;

COUNT(*) vs COUNT(column): Not the Same Thing

-- Add a nullable column to see the difference for real
ALTER TABLE dbo.Startup ADD acquired_by NVARCHAR(100) NULL;
UPDATE dbo.Startup SET acquired_by = 'BigTech Corp' WHERE name = 'PayFlow';

SELECT COUNT(*) AS total_rows,               -- 3 (every row counts)
       COUNT(acquired_by) AS acquired_count  -- 1 (only non-NULL values count)
FROM dbo.Startup;

These can give genuinely different answers on the same table. Always be deliberate about which one you actually mean — “how many rows” and “how many rows have a value here” are different questions, and mixing them up silently produces a wrong number, not an error.

COUNT(DISTINCT …): A Third Variant

SELECT COUNT(DISTINCT industry) AS unique_industries FROM dbo.Startup;

Counts how many distinct non-NULL values appear, not how many rows — useful for “how many different X do we have,” as opposed to “how many rows mention X.”

Aggregates and NULL: The Result Isn’t Always 0

-- On an empty result set (e.g. after a WHERE that matches nothing):
SELECT SUM(funding_usd) FROM dbo.Startup WHERE industry = 'Biotech'; -- returns NULL, not 0
SELECT COUNT(*) FROM dbo.Startup WHERE industry = 'Biotech';         -- returns 0, correctly

Combining Multiple Aggregates in One Query

SELECT
    COUNT(*) AS total_startups,
    SUM(funding_usd) AS total_funding,
    AVG(funding_usd) AS avg_funding,
    MAX(funding_usd) AS biggest_round,
    MIN(founded_year) AS oldest_founding_year
FROM dbo.Startup;

Every aggregate in a single SELECT (with no GROUP BY) computes over the same full set of matching rows — this is a common, efficient way to build a one-row “summary card” for a dashboard in a single round trip.

Key Takeaways

• COUNT, SUM, AVG, MIN, MAX collapse many rows into one summary value
• COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column specifically; COUNT(DISTINCT column) counts unique non-NULL values
• Aggregate functions (other than COUNT(*)) return NULL, not 0, when there’s nothing to aggregate — wrap in ISNULL/COALESCE if you need a real zero