Welcome back. In the last lesson, you mastered WHERE, ORDER BY, TOP, DISTINCT, and NULL handling.
Those skills help you extract the correct rows safely and present them clearly.
Now we move into a core DBA skill: summarizing data.
DBAs are constantly asked questions like:
“How many bookings today?”, “How much revenue this week?”, “Which statuses are increasing?”,
“Which customers have the most activity?”, “Are failed payments trending up?”
Those questions are not answered by reading raw rows one by one. They are answered with
aggregates and grouping.
Lesson description: You will learn the most important aggregate functions (COUNT, SUM, AVG, MIN, MAX),
how GROUP BY works, why every non-aggregated column must be grouped,
how HAVING differs from WHERE, how to avoid common mistakes (double counting, wrong grouping),
and professional DBA patterns for daily/weekly summaries and anomaly detection.
Aggregate functions compress many rows into a smaller set of results.
Instead of returning every booking, you can return:
DBA mindset: Aggregates are how you turn operational data into decisions.
COUNT returns how many rows exist in a set.
-- Total number of bookings SELECT COUNT(*) AS TotalBookings FROM dbo.Booking;
Important detail:
COUNT(*) counts rows.
COUNT(ColumnName) counts only rows where that column is NOT NULL.
-- Count customers who have a phone number recorded SELECT COUNT(Phone) AS CustomersWithPhone FROM dbo.Customer;
SUM adds numeric values across rows.
-- Total booking amount across all bookings SELECT SUM(TotalAmountUSD) AS TotalBookingAmountUSD FROM dbo.Booking;
AVG returns the average value.
-- Average booking total SELECT AVG(TotalAmountUSD) AS AvgBookingAmountUSD FROM dbo.Booking;
MIN and MAX find smallest and largest values. DBAs use them often for time ranges.
-- Earliest and latest pickup times
SELECT
MIN(PickupAt) AS EarliestPickup,
MAX(PickupAt) AS LatestPickup
FROM dbo.Booking;
GROUP BY creates groups of rows that share the same value(s), and then aggregates are calculated per group.
SELECT
Status,
COUNT(*) AS BookingCount
FROM dbo.Booking
GROUP BY Status
ORDER BY BookingCount DESC;
Key rule: In a SELECT with GROUP BY:
If you violate this, SQL Server throws an error. That error protects correctness.
You can group by more than one column. This is common in operational dashboards.
This requires joining to the Service table (you will learn joins deeply later, but this example is still readable).
SELECT
s.ServiceName,
b.Status,
COUNT(*) AS BookingCount,
SUM(b.TotalAmountUSD) AS TotalAmountUSD
FROM dbo.Booking AS b
JOIN dbo.Service AS s
ON b.ServiceID = s.ServiceID
GROUP BY
s.ServiceName,
b.Status
ORDER BY
s.ServiceName ASC,
b.Status ASC;
DBA note: GROUP BY is how you produce summary tables used in monitoring and business reporting.
This is one of the most important distinctions in SQL:
Think of it like this:
-- Count bookings by status, but only for bookings with totals >= 50
SELECT
Status,
COUNT(*) AS BookingCount
FROM dbo.Booking
WHERE TotalAmountUSD >= 50.00
GROUP BY Status
ORDER BY BookingCount DESC;
Example: “Show only statuses that have at least 10 bookings.”
SELECT
Status,
COUNT(*) AS BookingCount
FROM dbo.Booking
GROUP BY Status
HAVING COUNT(*) >= 10
ORDER BY BookingCount DESC;
DBA rule: If you need to filter by an aggregate result (COUNT/SUM/AVG), use HAVING.
If you try to SELECT a column that is not aggregated and not in GROUP BY, SQL Server blocks it.
That is good. It prevents incorrect reporting.
When you join tables, you can accidentally multiply rows.
Example: if a Booking has multiple Payment rows, joining Booking to Payment and then COUNT(*) can inflate booking counts.
DBA principle: Know what you are counting.
If you want “number of bookings,” count BookingID from Booking.
If you want “number of payments,” count PaymentID from Payment.
DISTINCT can hide a bad join or grouping mistake.
Use DISTINCT only when it matches the question you are actually answering.
Grouping by day is extremely common. The key is to group on a date value, not a datetime.
Here is a safe pattern for summary output.
-- Bookings per day (based on CreatedAt)
SELECT
CAST(CreatedAt AS DATE) AS CreatedDate,
COUNT(*) AS BookingCount,
SUM(TotalAmountUSD) AS TotalAmountUSD
FROM dbo.Booking
GROUP BY CAST(CreatedAt AS DATE)
ORDER BY CreatedDate DESC;
DBA note: Casting like this is fine for reporting. For large production tables, you often use a date dimension,
computed column, or different approach for performance—but the concept is correct for learning.
-- Customers with 5+ bookings (potential anomaly / VIP / fraud check depending on context)
SELECT
CustomerID,
COUNT(*) AS BookingCount
FROM dbo.Booking
GROUP BY CustomerID
HAVING COUNT(*) >= 5
ORDER BY BookingCount DESC;
DBAs often need a quick view of operational health. This query summarizes payment statuses.
SELECT
PaymentStatus,
COUNT(*) AS PaymentCount,
SUM(AmountUSD) AS TotalAmountUSD
FROM dbo.Payment
GROUP BY PaymentStatus
ORDER BY PaymentCount DESC;
Run these exercises and verify the output carefully:
DBA check: For each result, ask: “Is this answering the business question exactly, without double counting?”
Next, you will learn joins—the core skill for connecting tables correctly.
This is where many SQL beginners create wrong answers without realizing it.
You will learn how to join safely, how to spot duplicates caused by bad joins,
and how DBAs validate joins before using them in reports or troubleshooting.
Not a member yet? Register now
Are you a member? Login now