Skip to main content
2026-07-29 16:01
Database

Sum and Average

QueryValidateMedium
Try Interactive Demo

What you will learn

  • Every non-aggregate column in SELECT must appear in GROUP BY or the query is malformed (stricter in Postgres than MySQL)
  • COUNT(*) counts rows including NULL; COUNT(col) ignores NULL — subtle but changes business logic
  • HAVING filters groups (aggregates); WHERE filters rows. Using WHERE on an aggregate produces SQL error — test both filter positions

Description

Calculate total revenue and average order value.

Test Scenario

-- Your query:
SELECT
  SUM(total) as total_revenue,
  ROUND(AVG(total), 2) as avg_order_value,
  COUNT(*) as order_count
FROM orders;

-- Expected: Total, average, and count of all orders

Testing Tips

  • •SUM() adds all values
  • •AVG() calculates mean
  • •ROUND() for formatting decimals
// Playwright API Testing - Database
import { test, expect } from '@playwright/test';


test('Database query via API', async ({ request }) => {
  const response = await request.post('/api/database/query', {
    data: {
      sql: 'SELECT * FROM users WHERE id = ?',
      params: [1]
    }
  });


  expect(response.ok()).toBeTruthy();
  const { rows } = await response.json();
  expect(rows.length).toBeGreaterThan(0);
});

Challenge ID: sql-sum-avg

2 frameworks available