SQL Queries - Filtering, Sorting, and Aggregating

The SELECT Statement

The SELECT statement is the heart of SQL querying. Let's master it from basics to advanced.

Basic SELECT

-- All columns
SELECT * FROM Products;

-- Specific columns
SELECT ProductName, Price FROM Products;

-- With alias
SELECT
    ProductName AS Name,
    Price AS Cost
FROM Products;

-- Calculated columns
SELECT
    ProductName,
    Price,
    Price * 1.2 AS PriceWithTax
FROM Products;

WHERE Clause - Filtering Data

Comparison Operators

-- Equal
SELECT * FROM Products WHERE Price = 19.99;

-- Not equal
SELECT * FROM Products WHERE Price <> 19.99;
SELECT * FROM Products WHERE Price != 19.99; -- Same thing

-- Greater than / Less than
SELECT * FROM Products WHERE Price > 50;
SELECT * FROM Products WHERE Price < 100;
SELECT * FROM Products WHERE Price >= 50;
SELECT * FROM Products WHERE Price <= 100;

-- Between
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;

Logical Operators

-- AND
SELECT * FROM Products
WHERE Price > 10 AND Stock > 0;

-- OR
SELECT * FROM Products
WHERE CategoryID = 1 OR CategoryID = 2;

-- NOT
SELECT * FROM Products
WHERE NOT (Price > 100);

-- Combination
SELECT * FROM Products
WHERE (Price < 20 OR Price > 100)
  AND Stock > 0;

IN Operator

-- Instead of multiple ORs
SELECT * FROM Products
WHERE CategoryID IN (1, 2, 3, 5);

-- NOT IN
SELECT * FROM Products
WHERE CategoryID NOT IN (4, 6);

-- With subquery
SELECT * FROM Products
WHERE CategoryID IN (
    SELECT CategoryID
    FROM Categories
    WHERE IsActive = 1
);

LIKE Operator - Pattern Matching

-- Starts with 'Pro'
SELECT * FROM Products
WHERE ProductName LIKE 'Pro%';

-- Ends with 'er'
SELECT * FROM Products
WHERE ProductName LIKE '%er';

-- Contains 'phone'
SELECT * FROM Products
WHERE ProductName LIKE '%phone%';

-- Second character is 'r'
SELECT * FROM Products
WHERE ProductName LIKE '_r%';

-- Exactly 5 characters
SELECT * FROM Products
WHERE SKU LIKE '_____';

-- Case-insensitive by default in most collations
SELECT * FROM Products
WHERE ProductName LIKE '%PHONE%'; -- Matches 'phone', 'Phone', 'PHONE'

NULL Handling

-- Check for NULL
SELECT * FROM Products
WHERE Description IS NULL;

-- Check for NOT NULL
SELECT * FROM Products
WHERE Description IS NOT NULL;

-- COALESCE - return first non-null value
SELECT
    ProductName,
    COALESCE(Description, 'No description available') AS Description
FROM Products;

-- ISNULL - T-SQL specific
SELECT
    ProductName,
    ISNULL(Description, 'No description') AS Description
FROM Products;

ORDER BY - Sorting Results

-- Ascending (default)
SELECT * FROM Products
ORDER BY Price;

SELECT * FROM Products
ORDER BY Price ASC;

-- Descending
SELECT * FROM Products
ORDER BY Price DESC;

-- Multiple columns
SELECT * FROM Products
ORDER BY CategoryID ASC, Price DESC;

-- By column position (avoid in production)
SELECT ProductName, Price FROM Products
ORDER BY 2 DESC; -- Orders by Price (2nd column)

-- By expression
SELECT * FROM Products
ORDER BY LEN(ProductName) DESC;

-- With CASE
SELECT * FROM Products
ORDER BY
    CASE CategoryID
        WHEN 1 THEN 1
        WHEN 5 THEN 2
        ELSE 3
    END;

DISTINCT - Unique Values

-- Unique categories
SELECT DISTINCT CategoryID
FROM Products;

-- Unique combinations
SELECT DISTINCT CategoryID, SupplierID
FROM Products;

-- Count unique values
SELECT COUNT(DISTINCT CategoryID) AS UniqueCategoriesCount
FROM Products;

TOP - Limiting Results

-- First 10 rows
SELECT TOP 10 * FROM Products;

-- First 10 with ORDER BY
SELECT TOP 10 * FROM Products
ORDER BY Price DESC;

-- Top 5 percent
SELECT TOP 5 PERCENT * FROM Products
ORDER BY Price DESC;

-- With TIES (includes ties for last position)
SELECT TOP 10 WITH TIES * FROM Products
ORDER BY Price DESC;

OFFSET and FETCH - Pagination

-- Skip first 10, get next 10 (rows 11-20)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Page 1 (rows 1-20)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

-- Page 2 (rows 21-40)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY;

-- Page 3 (rows 41-60)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

Aggregate Functions

Basic Aggregates

-- COUNT
SELECT COUNT(*) AS TotalProducts FROM Products;
SELECT COUNT(Description) AS ProductsWithDescription FROM Products;
SELECT COUNT(DISTINCT CategoryID) AS UniqueCategories FROM Products;

-- SUM
SELECT SUM(Price) AS TotalValue FROM Products;
SELECT SUM(Stock * Price) AS TotalInventoryValue FROM Products;

-- AVG
SELECT AVG(Price) AS AveragePrice FROM Products;

-- MIN and MAX
SELECT MIN(Price) AS CheapestProduct FROM Products;
SELECT MAX(Price) AS MostExpensive FROM Products;

-- Multiple aggregates
SELECT
    COUNT(*) AS TotalProducts,
    AVG(Price) AS AvgPrice,
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice,
    SUM(Stock) AS TotalStock
FROM Products;

GROUP BY

-- Group by single column
SELECT
    CategoryID,
    COUNT(*) AS ProductCount,
    AVG(Price) AS AvgPrice
FROM Products
GROUP BY CategoryID;

-- Group by multiple columns
SELECT
    CategoryID,
    SupplierID,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryID, SupplierID;

-- With ORDER BY
SELECT
    CategoryID,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryID
ORDER BY ProductCount DESC;

HAVING - Filtering Groups

-- Categories with more than 5 products
SELECT
    CategoryID,
    COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryID
HAVING COUNT(*) > 5;

-- Categories where average price > 50
SELECT
    CategoryID,
    AVG(Price) AS AvgPrice
FROM Products
GROUP BY CategoryID
HAVING AVG(Price) > 50;

-- Combination of WHERE and HAVING
SELECT
    CategoryID,
    COUNT(*) AS ProductCount
FROM Products
WHERE IsActive = 1              -- Filter before grouping
GROUP BY CategoryID
HAVING COUNT(*) > 5             -- Filter after grouping
ORDER BY ProductCount DESC;

String Functions

-- Concatenation
SELECT
    FirstName + ' ' + LastName AS FullName
FROM Users;

-- CONCAT (handles NULL better)
SELECT
    CONCAT(FirstName, ' ', LastName) AS FullName
FROM Users;

-- CONCAT_WS (with separator)
SELECT
    CONCAT_WS(' ', FirstName, MiddleName, LastName) AS FullName
FROM Users;

-- UPPER and LOWER
SELECT UPPER(ProductName) FROM Products;
SELECT LOWER(ProductName) FROM Products;

-- LEN and DATALENGTH
SELECT LEN(ProductName) FROM Products;

-- SUBSTRING
SELECT SUBSTRING(ProductName, 1, 10) AS ShortName FROM Products;

-- LEFT and RIGHT
SELECT LEFT(ProductName, 5) FROM Products;
SELECT RIGHT(ProductName, 5) FROM Products;

-- TRIM, LTRIM, RTRIM
SELECT TRIM('  Hello  ') AS Trimmed;
SELECT LTRIM('  Hello') AS LeftTrim;
SELECT RTRIM('Hello  ') AS RightTrim;

-- REPLACE
SELECT REPLACE(ProductName, 'old', 'new') FROM Products;

-- CHARINDEX (find position)
SELECT CHARINDEX('phone', ProductName) FROM Products;

Date Functions

-- Current date/time
SELECT GETDATE() AS CurrentDateTime;
SELECT SYSDATETIME() AS CurrentDateTime2; -- More precise

-- Date parts
SELECT
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    DAY(OrderDate) AS Day
FROM Orders;

-- DATEPART
SELECT DATEPART(QUARTER, OrderDate) AS Quarter FROM Orders;
SELECT DATEPART(WEEK, OrderDate) AS WeekNumber FROM Orders;
SELECT DATEPART(WEEKDAY, OrderDate) AS DayOfWeek FROM Orders;

-- DATENAME
SELECT DATENAME(MONTH, OrderDate) AS MonthName FROM Orders;
SELECT DATENAME(WEEKDAY, OrderDate) AS DayName FROM Orders;

-- DATEADD (add interval)
SELECT DATEADD(DAY, 7, OrderDate) AS DueDatePlus7 FROM Orders;
SELECT DATEADD(MONTH, 1, GETDATE()) AS NextMonth;
SELECT DATEADD(YEAR, -1, GETDATE()) AS LastYear;

-- DATEDIFF (difference between dates)
SELECT DATEDIFF(DAY, OrderDate, ShipDate) AS DaysToShip FROM Orders;
SELECT DATEDIFF(HOUR, CreatedDate, GETDATE()) AS HoursSinceCreation FROM Users;

-- Format dates
SELECT FORMAT(OrderDate, 'yyyy-MM-dd') AS ISODate FROM Orders;
SELECT FORMAT(OrderDate, 'MMMM dd, yyyy') AS FriendlyDate FROM Orders;
SELECT CONVERT(VARCHAR, OrderDate, 101) AS USDate FROM Orders; -- MM/DD/YYYY

Conditional Logic

CASE Statement

-- Simple CASE
SELECT
    ProductName,
    Price,
    CASE
        WHEN Price < 20 THEN 'Budget'
        WHEN Price < 50 THEN 'Mid-Range'
        WHEN Price < 100 THEN 'Premium'
        ELSE 'Luxury'
    END AS PriceCategory
FROM Products;

-- CASE in aggregate
SELECT
    CategoryID,
    SUM(CASE WHEN Stock > 0 THEN 1 ELSE 0 END) AS InStock,
    SUM(CASE WHEN Stock = 0 THEN 1 ELSE 0 END) AS OutOfStock
FROM Products
GROUP BY CategoryID;

-- CASE in WHERE
SELECT * FROM Products
WHERE
    CASE
        WHEN CategoryID = 1 THEN Price > 50
        WHEN CategoryID = 2 THEN Price > 30
        ELSE Price > 20
    END = 1;

IIF (Inline IF)

-- T-SQL shorthand for simple CASE
SELECT
    ProductName,
    IIF(Stock > 0, 'Available', 'Out of Stock') AS Availability
FROM Products;

-- Nested IIF
SELECT
    ProductName,
    IIF(Price < 20, 'Cheap',
        IIF(Price < 50, 'Moderate', 'Expensive')
    ) AS PriceRange
FROM Products;

Subqueries

-- In WHERE clause
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

-- In SELECT clause
SELECT
    ProductName,
    Price,
    (SELECT AVG(Price) FROM Products) AS AvgPrice,
    Price - (SELECT AVG(Price) FROM Products) AS DiffFromAvg
FROM Products;

-- Correlated subquery
SELECT
    p1.ProductName,
    p1.Price,
    (
        SELECT AVG(p2.Price)
        FROM Products p2
        WHERE p2.CategoryID = p1.CategoryID
    ) AS CategoryAvgPrice
FROM Products p1;

-- EXISTS
SELECT * FROM Categories c
WHERE EXISTS (
    SELECT 1 FROM Products p
    WHERE p.CategoryID = c.CategoryID
);

-- NOT EXISTS
SELECT * FROM Categories c
WHERE NOT EXISTS (
    SELECT 1 FROM Products p
    WHERE p.CategoryID = c.CategoryID
);

Common Table Expressions (CTE)

-- Basic CTE
WITH HighPriceProducts AS (
    SELECT *
    FROM Products
    WHERE Price > 100
)
SELECT * FROM HighPriceProducts
WHERE Stock > 0;

-- Multiple CTEs
WITH
    AvgPrices AS (
        SELECT CategoryID, AVG(Price) AS AvgPrice
        FROM Products
        GROUP BY CategoryID
    ),
    ProductsWithAvg AS (
        SELECT
            p.ProductName,
            p.Price,
            ap.AvgPrice
        FROM Products p
        INNER JOIN AvgPrices ap ON p.CategoryID = ap.CategoryID
    )
SELECT * FROM ProductsWithAvg
WHERE Price > AvgPrice;

Performance Tips

-- Use column names instead of *
SELECT ProductID, ProductName, Price FROM Products;

-- Filter early with WHERE
SELECT * FROM Products
WHERE IsActive = 1; -- Filter first

-- Use appropriate indexes
CREATE INDEX IX_Products_Price ON Products(Price);

-- Avoid functions on indexed columns in WHERE
-- Bad
SELECT * FROM Products WHERE YEAR(CreatedDate) = 2025;
-- Good
SELECT * FROM Products
WHERE CreatedDate >= '2025-01-01'
  AND CreatedDate < '2026-01-01';

-- Use EXISTS instead of COUNT
-- Bad
IF (SELECT COUNT(*) FROM Products WHERE ProductID = 1) > 0
-- Good
IF EXISTS (SELECT 1 FROM Products WHERE ProductID = 1)

Next Topics

This covers querying fundamentals. Next articles will explore: - JOINs: Combining data from multiple tables - Reporting: Advanced aggregations and pivots - Optimization: Execution plans and indexing strategies

Practice these concepts - they're the foundation for all SQL work!

SQL Queries - Filtering, Sorting, and Aggregating

SQL Queries - Filtering, Sorting, and Aggregating

The SELECT Statement

Basic SELECT

WHERE Clause - Filtering Data

Comparison Operators

Logical Operators

IN Operator

LIKE Operator - Pattern Matching

NULL Handling

ORDER BY - Sorting Results

DISTINCT - Unique Values

TOP - Limiting Results

Aggregate Functions

Basic Aggregates

GROUP BY

HAVING - Filtering Groups

String Functions

Date Functions

Conditional Logic

CASE Statement

IIF (Inline IF)

Subqueries

Common Table Expressions (CTE)

Performance Tips

Next Topics

Quick Nav

Related Articles