SQL Total Queries Cheat Sheet: 25 Essential Patterns

Most enterprise developers spend over 60% of their time reverse-engineering someone else's SQL instead of building new features. When nightly revenue roll-ups crawl past their SLA and dashboards timeout waiting for yesterday's numbers, the cost becomes impossible to ignore. Long, unindexed aggregates strain hardware, inflate cloud bills, and leave finance teams waiting while reports spin endlessly.

This comprehensive cheat sheet delivers 25 SQL total queries that actually work at enterprise scale. Each pattern includes performance notes, real-world context, and "Augment Boost" tips showing how AI agents can automate the tedious optimization work. Whether you're summing billions of rows, calculating month-to-date subtotals, or stitching counts across multiple databases, these examples provide the foundation for faster, more reliable data workflows.

Why Fast SQL Totals Determine Your Success as a Developer

Picture this: the nightly revenue roll-up is still churning when you arrive at work, and the finance team needs numbers before the market opens. That single aggregate query blocks reports, deployments, and your morning coffee. On terabyte-scale tables, even a simple SUM() can crawl if the execution plan isn't perfect. Digging through a 1,500-line stored procedure to find the bottleneck can consume entire sprint cycles.

Enterprise totals fail for two critical reasons. First, performance degrades as data volumes climb into billions of rows. Join-heavy aggregates become resource hogs, forcing teams into full-time query tuning, index management, and optimizer coaxing. Query performance optimization becomes essential when every minute of delay skews daily revenue snapshots.

Second, data fragmentation creates chaos. Revenue data rarely lives in one neat schema. It's scattered across legacy SQL Server instances, cloud warehouses, and regional databases with clashing schemas. Moving figures between these systems introduces ETL lag, schema mismatches, and broken joins. The result is totals that arrive late, contain errors, or both, eroding trust in every downstream dashboard.

A curated SQL cheat sheet eliminates this complexity. When teams have battle-tested patterns for GROUP BY ROLLUP, windowed running sums, and cross-database unions, they stop reinventing solutions. Each snippet serves as both a performance baseline and a mental model that adapts to new requirements instead of requiring rewrites from scratch.

The difference between fast and slow totals ultimately determines whether leadership trusts the data enough to make critical business decisions. Give executives numbers they can rely on, and development teams gain breathing room to build instead of constantly babysitting the data pipeline.

25 Essential SQL Total Query Patterns That Actually Work

Every query below solves a specific problem enterprise developers face daily: understanding table contents, rolling up numbers executives trust, and doing it fast enough to meet SLA windows. Each example shows the SQL, explains why it works at scale, and includes an "Augment Boost" note highlighting where AI agents catch the gotchas that typically cause production issues.

All examples use ANSI standard SQL for compatibility across PostgreSQL, SQL Server, Oracle, and MySQL. When engine-specific optimizations matter (index hints, partition pruning, parallel execution plans), we'll highlight them with performance analysis links.

Basic Aggregates: Foundation Queries (1-5)

Measurement drives improvement. These five queries handle the fundamental counting and summing operations that underpin every business metric.

Query 1: Simple Row Count

SELECT COUNT(*) AS total_rows FROM orders;

Purpose: Verify table size before attempting heavy joins. At billion-row scale, COUNT(*) can hammer I/O without proper indexing. ThoughtSpot's tuning guide shows how primary key indexes avoid full table scans.

Augment Boost: Detects when metadata shortcuts are safe and suggests lighter alternatives automatically.

Query 2: SUM for Financial Totals

SELECT SUM(amount) AS total_revenue

FROM payments

WHERE paid_at >= CURRENT_DATE - INTERVAL '1 day';

Daily revenue roll-ups can blow past time windows without proper indexing on the amount column. Acceldata's benchmarks demonstrate 3-4× performance improvements once the missing index exists.

Augment Boost: Suggests adding missing indexes as part of current pull requests, not follow-up work.

Query 3: AVG for Performance Metrics

SELECT AVG(render_ms) AS avg_page_time

FROM page_views

WHERE event_date = CURRENT_DATE;

Averages hide outliers that destroy user experience. Pair this with percentile functions (see Query 14) for complete performance pictures.

Augment Boost: Warns when fat-tailed distributions make AVG misleading and suggests PERCENTILE_CONT(0.95) instead.

Query 4: MIN/MAX for Range Checks

SELECT MIN(created_at) AS first_order,

MAX(created_at) AS latest_order

FROM orders;

Data range knowledge guides partitioning decisions critical for petabyte tables according to established big data engineering principles.

Augment Boost: Identifies partition candidates and drafts the DDL automatically.

Query 5: Multiple Aggregates at Once

SELECT
  COUNT(*) AS rows,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_ticket
FROM payments
WHERE status = 'SUCCESS';

Combining aggregates lets the query planner scan once, keeping cache lines hot and improving performance.

Augment Boost: Consolidates scattered single-purpose queries into efficient multi-aggregate blocks.

Group Totals: Business Intelligence Queries (6-10)

Grouping transforms raw counts into actionable business insights. These patterns power dashboards and critical executive reports.

Query 6: Basic GROUP BY

SELECT country, SUM(amount) AS revenue

FROM payments

GROUP BY country;

Add a composite index on (country, amount) to prevent expensive hash operations during grouping.

Augment Boost: Checks existing indexes and warns when column order mismatches query patterns.

Query 7: Multi-Column Grouping

SELECT country, channel, SUM(amount) AS revenue

FROM payments

GROUP BY country, channel;

Perfect for marketing attribution analysis. Monitor cardinality carefully as it explodes with additional dimensions.

Augment Boost: Previews result set size to catch "exploding group" problems before production deployment.

Query 8: HAVING to Filter Groups

SELECT country,
       COUNT(*) AS orders
FROM orders
GROUP BY country
HAVING COUNT(*) > 1000;

Filtering after aggregation keeps SQL clean and avoids complex client-side loops.

Augment Boost: Automatically moves filters from WHERE to HAVING when filtering on aggregated columns.

Query 9: ROLLUP for Subtotals

SELECT country,
       channel,
       SUM(amount) AS revenue
FROM payments
GROUP BY ROLLUP (country, channel);

One query pass generates row-level, subtotal, and grand total data, replacing three separate queries that BI tools traditionally run. Oracle's data warehouse documentation covers this extensively.

Augment Boost: Auto-labels NULL subtotal rows so downstream reports handle hierarchical data correctly.

Query 10: CUBE for Multi-Dimensional Totals

SELECT country,
       channel,
       product_line,
       SUM(amount) AS revenue
FROM payments
GROUP BY CUBE (country, channel, product_line);

Expected output grows exponentially with dimensions. Use carefully or combine with HAVING clauses for manageability.

Augment Boost: Calculates row explosion impact and suggests pruning low-value combinations.

Window Totals: Advanced Analytics (11-15)

Window functions compute running, ranked, or comparative metrics without collapsing rows. They represent SQL's most powerful feature for analytics.

Query 11: Running SUM

SELECT
  order_id,
  order_date,
  SUM(amount) OVER (ORDER BY order_date) AS running_revenue
FROM orders;

No temporary tables or application loops required, just clean SQL that performs well.

Augment Boost: Catches missing ORDER BY clauses that cause the classic "why are my running totals random?" bug.

Query 12: PARTITION BY Segments

SELECT
  country,
  order_date,
  SUM(amount) OVER (PARTITION BY country
                    ORDER BY order_date) AS country_running_rev
FROM orders;

Keeps each country's running total completely isolated from others.

Augment Boost: Converts legacy self-joins into cleaner window function logic automatically.

Query 13: ROW_NUMBER for Top-N per Group

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY country
                            ORDER BY amount DESC) AS rn
  FROM payments
)
SELECT *
FROM ranked
WHERE rn = 1;

Perfect for "highest-value order by country" scenarios without complex correlated subqueries.

Augment Boost: Replaces brittle correlated subqueries with this cleaner, more performant pattern.

Query 14: NTILE for Percentile Buckets

SELECT
  order_id,
  NTILE(100) OVER (ORDER BY amount) AS percentile_bucket
FROM orders;

Essential for performance SLAs that flag the slowest 1% of requests, following GeeksforGeeks tuning advice.

Augment Boost: Suggests bucket counts that align with existing SLA thresholds.

Query 15: Moving Window Frames

SELECT
  order_date,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_revenue
FROM daily_revenue;

Native moving averages without CTE complexity or temporary table overhead.

Augment Boost: Warns when window frames cross month boundaries that distort financial calculations.

Conditional Totals: Business Logic in SQL (16-18)

When not every row should count equally, conditional aggregates keep business logic close to the data for better performance.

Query 16: CASE Inside Aggregates

SELECT
  SUM(CASE WHEN channel = 'mobile' THEN amount ELSE 0 END) AS mobile_rev,
  SUM(CASE WHEN channel = 'web'    THEN amount ELSE 0 END) AS web_rev
FROM payments;

One table scan produces multiple business metrics efficiently.

Augment Boost: Consolidates repeated filtered SUMs into this more efficient CASE pattern.

Query 17: Filtered Aggregates

SELECT
  COUNT(*) FILTER (WHERE status = 'FAILED') AS failed_orders
FROM orders;

PostgreSQL syntax. MySQL and SQL Server require CASE statements for equivalent functionality.

Augment Boost: Picks the correct syntax based on detected database engine.

Query 18: Multi-Condition Logic

SELECT
  COUNT(*) AS total,
  SUM(CASE
        WHEN status = 'FAILED' AND retry = TRUE THEN 1
        ELSE 0
      END) AS auto_retries
FROM orders;

Tracks complex KPIs without ETL overhead or application-side processing.

Augment Boost: Detects duplicated business logic across services and centralizes it in SQL.

Running Totals: Financial Reporting (19-22)

Finance teams depend on cumulative calculations. These patterns deliver them reliably at enterprise scale.

Query 19: Cumulative Year Totals

SELECT
  month,
  SUM(revenue) OVER (ORDER BY month) AS ytd_revenue
FROM monthly_revenue
WHERE year = 2025;

Meets the "year-to-date by any cut-off" requirement that financial analytics demands.

Augment Boost: Parameterizes the year filter so queries automatically work in future periods.

Query 20: 30-Day Moving Average

SELECT
  day,
  AVG(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS ma_30d
FROM daily_revenue;

Smooths volatility for executive dashboards without requiring external tools.

Augment Boost: Flags window sizes that undersample smaller datasets.

Query 21: Month-to-Date with Resets

SELECT
  day,
  SUM(revenue) OVER (
    PARTITION BY EXTRACT(MONTH FROM day)
    ORDER BY day
  ) AS mtd_revenue
FROM daily_revenue;

PARTITION handles month boundaries automatically without procedural code complexity.

Augment Boost: Converts complex date math from application code into this single, efficient query.

Query 22: Period-over-Period Comparison

WITH ranked AS (
  SELECT
    month,
    SUM(revenue) AS rev
  FROM monthly_revenue
  GROUP BY month
),
lagged AS (
  SELECT
    month,
    rev,
    LAG(rev) OVER (ORDER BY month) AS prev_rev
  FROM ranked
)
SELECT
  month,
  rev,
  (rev - prev_rev) AS delta
FROM lagged;

Quantifies growth trends without Excel exports or manual calculations.

Augment Boost: Builds the CTE chain automatically once you describe "compare to previous period."

Cross-Database Totals: Enterprise Integration (23-25)

Data rarely lives in one database. These patterns maintain consistency across systems without compromising performance.

Query 23: UNION-Based Rollups

SELECT 'mysql' AS source, COUNT(*) FROM mysql_db.orders

UNION ALL

SELECT 'oracle' AS source, COUNT(*) FROM oracle_db.orders;

Simple and portable, but watch for schema mismatches between systems that can cause runtime errors.

Augment Boost: Generates the boilerplate once you specify data sources and handles schema validation.

Query 24: CROSS JOIN Counts

SELECT
  a.total_orders * b.total_customers AS order_customer_pairs
FROM
  (SELECT COUNT(*) AS total_orders    FROM orders)   a,
  (SELECT COUNT(*) AS total_customers FROM customers) b;

Useful for capacity planning when combinations matter for scaling decisions.

Augment Boost: Warns when multiplication results exceed integer limits.

Query 25: Federated Totals via External Tables

SELECT
  region,
  SUM(sales) AS revenue
FROM external_postgres.sales  -- foreign table
GROUP BY region;

Uses SQL Server's external tables or PostgreSQL FDW features. Accutive Security identifies this as essential for breaking down data silos.

Augment Boost: Configures foreign table definitions and handles credentials without exposing passwords in code.

Indexing aggregated columns and pruning partitions aren't optional optimizations, they're the guardrails that keep these queries under SLA budgets. Follow the indexing strategies in Idera's 2025 tuning guide and monitor execution plans continuously.

With these twenty-five patterns and an AI agent that knows when to apply each one, teams can stop reverse-engineering legacy queries and start delivering reliable totals on the first attempt.

How Manual SQL Workflows Compare to AI-Assisted Development

When teams run dozens of revenue roll-up queries and each one takes minutes to complete, every workflow delay directly impacts nightly dashboard delivery. The familiar cycle persists: wait for a long join to complete, discover a missing index or overlooked edge case that breaks compliance rules, then scramble to fix it before morning reports. Fast SQL workflows become mandatory when shipping quarter-end reports to the CFO. Slow, error-prone SQL costs real money in compute cycles, developer hours, and regulatory risk.

Performance Benchmarks: Manual vs AI-Assisted

Time to Write Complex Queries

• Manual: Hours per query, sometimes days for cross-database joins requiring extensive column hunting and execution plan debugging
• Augment-Assisted: Complete queries generated in seconds, filtered to schema specifications, enabling review instead of drafting from scratch

Error Rates in Development

• Manual: Typos, forgotten joins, and mismatched data types slip into production, burning sprint cycles for fixes
• Augment-Assisted: Context-aware suggestions catch mistakes during development, before production deployment

Query Optimization

• Manual: Depends on individual expertise and late-stage tuning, missed indexes kill performance
• Augment-Assisted: Suggestions include indexing hints, filter-early patterns, and partition advice during initial development

Security and Compliance

• Manual: Grant-wide SELECTs persist, dynamic SQL creates injection risks, manual audits struggle against data silos
• Augment-Assisted: Defaults to parameterized queries, least-privilege views, and maintains auditable trails backed by SOC 2 Type II certification with zero training on customer code

Developer Onboarding

• Manual: Weeks of digging through legacy scripts before safe edits feel possible
• Augment-Assisted: Shared context engine surfaces table relationships, stored procedures, and migration history instantly, enabling meaningful SQL commits within days

Cross-Database Integration

• Manual: Manual UNIONs, brittle ETL, and separate auth flows slow delivery
• Augment-Assisted: Federated query patterns stitch schemas together without custom glue code

Production-Ready SQL Optimization Strategies

Nothing destroys a nightly roll-up faster than a rogue aggregate scanning billion-row tables. The symptoms become painfully familiar: dashboards timeout, cloud bills spike, and everyone points fingers at the database. Most production slowdowns trace back to a handful of fixable patterns that experienced teams learn to avoid.

Index Strategy for Enterprise Scale

Start with targeted indexes on columns used for aggregation or filtering. A well-placed composite index can cut scan time from minutes to seconds, especially when aggregations leverage the same keys used for table joins. Proper indexing also reduces I/O costs significantly in usage-based cloud environments.

When Augment agents review code, they flag missing indexes during pull requests and generate exact CREATE INDEX statements needed for optimal performance. This proactive approach prevents performance issues rather than fixing them after deployment.

Partitioning for Large Fact Tables

Horizontal partitioning deserves equal attention for large fact tables. Let the optimizer touch only partitions relevant to date ranges or business units. Studies show this technique can reduce query latency by double digits. The key is ensuring queries include partition filters to avoid full-table scans.

Augment handles this automatically when adding new partition keys, finding dependent queries and rewriting them with proper filters to maintain performance.

Materialized Views for Repeated Aggregates

For aggregates that run repeatedly, materialized views become essential. These can refresh incrementally and serve pre-computed totals to dozens of concurrent analysts. Schedule view refresh jobs tied to CI pipelines so every schema change triggers safe rebuilds.

Parameterized Queries for Security and Performance

Parameterized queries matter more than most teams realize. Beyond obvious security benefits, Red-Gate's analysis shows better plan reuse and execution consistency. Agents excel at converting string-built SQL into parameterized statements and surfacing hard-coded literals lurking in legacy code.

Query Plan Analysis

Don't ignore actual query plans. Study them and provide hints when necessary. SQL Server's optimizer evaluates multiple strategies before selecting the cheapest option, but occasional hints or index rewrites remain necessary. Having annotated EXPLAIN plans attached to pull request summaries, pointing out expensive operators and suggesting improvements, makes optimization decisions much clearer.

Statistics Maintenance

Keep statistics fresh to stay in the optimizer's good graces. Outdated statistics rank among the most common causes of plan regression. Monitor row-count thresholds and automate stats updates during low-traffic windows rather than waiting for performance degradation.

Incremental Processing

Process totals incrementally rather than in monolithic batches. Change Data Capture or windowed ETL jobs shrink the data touched each run, lowering contention and meeting tighter SLAs. Generate delta queries based on the last successful watermark and roll back automatically when anomalies appear.

Continuous Monitoring

Instrument and monitor every production query through continuous profiling. Wait stats, I/O metrics, and buffer cache hits catch regressions before business users notice. Stream slow-query logs to shared channels and enable teams or integrated tools to propose hotfixes with test coverage before the next deployment.

Implementing these practices ensures nightly totals behave predictably, executives stop asking why reports load slowly, and development teams regain mental bandwidth to tackle more interesting problems than babysitting aggregates.

How Augment Transforms SQL Development Workflows

The familiar pattern persists across enterprise teams: a seemingly simple change to a revenue roll-up query turns into hours of spelunking through migration scripts, ORM models, and half-documented views spread across multiple repositories. That context-switching tax becomes brutal as database footprints expand. Augment's AI agents solve this problem by acting like the tireless teammate who remembers every table alias and schema evolution.

Proprietary Context Engine

The Proprietary Context Engine maintains an always-current index of entire codebases, including scattered .sql files that live outside main repositories. Because it parses dependencies and call chains, the engine can autocomplete a SUM() window function while simultaneously flagging downstream materialized views that need matching schema updates. Instead of copy-pasting column names from browser tabs, developers get suggestions that already fit their schema, data types, and naming conventions.

Remote Agent Infrastructure

Augment's Remote Agent Infrastructure operates as a cluster of background workers that can open pull requests, generate migration scripts, and iterate on failing test suites without blocking laptops. Kick off an agent to "add quarterly totals" and it will scan every query touching the fact_revenue table, refactor window aggregations where needed, create idempotent migrations with proper rollbacks, and surface concise PR descriptions so reviewers see intent, not noise.

Because agents operate on full repositories rather than code snippets pasted into chat windows, they can spot cross-repository patterns. When the same anti-pattern appears in a Python ETL job and a Java microservice, Augment proposes a single fix that keeps both sides consistent. That cross-pollination becomes nearly impossible to achieve with manual searches or single-file copilots.

Enterprise Security and Compliance

Security remains built-in, not bolted-on. All processing happens within a SOC 2 Type II certified environment, and models run on encrypted context without training on customer data. Agents generate parameterized queries by default, steering teams away from dynamic SQL that invites injection attacks. Permissions map directly to Git providers, so the same RBAC rules that gate production branches also gate AI-driven changes.

Measured Results

Real-world numbers validate the approach. A fintech firm pushing nightly ETL jobs through a 10-terabyte warehouse cut runtime by 40% after letting Augment rewrite their cumulative totals logic and partition strategy. This freed a full hour in their maintenance window and slashed cloud spending. Developers there now treat the agent as the first reviewer on every SQL-heavy pull request. Humans mostly fine-tune edge cases instead of chasing missing commas.

Workflow Transformation

Teams report two immediate improvements with this approach. Speed increases dramatically. Boilerplate query writing drops from minutes to keystrokes, and complex migrations that once spanned several stand-ups often close within the same afternoon. Accuracy improves equally, with fewer regression rollbacks because agents catch orphaned views, stale triggers, and index mismatches during code generation, not in production.

A staff engineer captured it perfectly: "It feels like pair programming with someone who has the entire schema, git history, and ticket backlog loaded into short-term memory." Teams still steer the ship, but suddenly the map, compass, and weather report stay one keystroke away.

Start Building Faster SQL Workflows Today

These 25 SQL queries handle the fundamentals of enterprise data aggregation: basic aggregates, window functions, cross-database roll-ups, plus the production optimization strategies that keep them fast. No more hunting Stack Overflow at 2 a.m. when deadline pressure demands solid total queries.

Code samples provide the foundation, but when deadlines hit and schemas evolve, Augment Code's AI agents handle the repetitive work: generating migration scripts, catching missing indexes, opening pull requests with fixes, all while maintaining SOC 2 Type II compliance. That fintech team's 40% ETL runtime improvement freed their engineers to focus on features instead of fighting slow queries.

Ready to transform your SQL workflow? Start a secure 7-day Augment trial and test these queries in your environment. Run your nightly revenue roll-up twice: once manually, once with an Augment agent. Compare the build time, error count, and cognitive overhead. If manual development still wins, walk away. If not, you've just streamlined your SQL workflow and bought back hours in your week.

Try Augment Code free for 7 days and experience the difference between wrestling with legacy SQL and letting AI handle the heavy lifting while you ship the features that matter.