Querying Soql

/**
 * AGGREGATE QUERY PATTERNS
 *
 * SOQL aggregate functions:
 * - COUNT(), COUNT(field), COUNT_DISTINCT(field)
 * - SUM(field), AVG(field), MIN(field), MAX(field)
 *
 * Use with GROUP BY for grouping results.
 */

// ═══════════════════════════════════════════════════════════════════════════
// BASIC COUNT
// ═══════════════════════════════════════════════════════════════════════════

-- Count all records
SELECT COUNT() FROM Account

-- Count with filter
SELECT COUNT() FROM Contact WHERE Email != null

-- Count with field alias
SELECT COUNT(Id) total FROM Account

-- Count distinct values
SELECT COUNT_DISTINCT(Industry) FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// SUM, AVG, MIN, MAX
// ═══════════════════════════════════════════════════════════════════════════

-- Sum of amounts
SELECT SUM(Amount) totalAmount FROM Opportunity WHERE IsClosed = true

-- Average amount
SELECT AVG(Amount) avgAmount FROM Opportunity WHERE StageName = 'Closed Won'

-- Min and Max
SELECT MIN(Amount) smallest, MAX(Amount) largest
FROM Opportunity
WHERE IsClosed = true

-- Combined aggregates
SELECT
    COUNT(Id) totalOpps,
    SUM(Amount) totalAmount,
    AVG(Amount) avgAmount,
    MIN(Amount) minAmount,
    MAX(Amount) maxAmount
FROM Opportunity
WHERE StageName = 'Closed Won'
AND CALENDAR_YEAR(CloseDate) = 2024

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY
// ═══════════════════════════════════════════════════════════════════════════

-- Count by single field
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry

-- Count by multiple fields
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY Industry, Type

-- Sum by category
SELECT StageName, SUM(Amount)
FROM Opportunity
GROUP BY StageName

-- Average by owner
SELECT OwnerId, AVG(Amount)
FROM Opportunity
GROUP BY OwnerId

-- Aggregate by date part
SELECT CALENDAR_MONTH(CloseDate), SUM(Amount)
FROM Opportunity
WHERE CALENDAR_YEAR(CloseDate) = 2024
GROUP BY CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_MONTH(CloseDate)

-- Year and Month grouping
SELECT CALENDAR_YEAR(CloseDate) yr, CALENDAR_MONTH(CloseDate) mn, SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)

// ═══════════════════════════════════════════════════════════════════════════
// HAVING CLAUSE
// ═══════════════════════════════════════════════════════════════════════════

-- Filter groups by aggregate result
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP BY Industry
HAVING COUNT(Id) > 10

-- Multiple HAVING conditions
SELECT OwnerId, SUM(Amount) total, COUNT(Id) cnt
FROM Opportunity
WHERE StageName = 'Closed Won'
GROUP BY OwnerId
HAVING SUM(Amount) > 100000
AND COUNT(Id) >= 5

-- HAVING with different aggregate than SELECT
SELECT LeadSource, COUNT(Id)
FROM Lead
GROUP BY LeadSource
HAVING AVG(NumberOfEmployees) > 100

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY ROLLUP (Subtotals)
// ═══════════════════════════════════════════════════════════════════════════

-- Single rollup
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY ROLLUP(Industry)
-- Returns: individual industries + grand total (null Industry row)

-- Multiple field rollup
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY ROLLUP(Industry, Type)
-- Returns: Industry+Type combos, Industry subtotals, grand total

-- With SUM
SELECT StageName, CALENDAR_MONTH(CloseDate), SUM(Amount)
FROM Opportunity
WHERE CALENDAR_YEAR(CloseDate) = 2024
GROUP BY ROLLUP(StageName, CALENDAR_MONTH(CloseDate))

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY CUBE (All Combinations)
// ═══════════════════════════════════════════════════════════════════════════

-- All dimension combinations
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY CUBE(Industry, Type)
-- Returns: Industry+Type, Industry only, Typ