Learn with Abdou

INTERACTIVE LECTURES

CASE & UNION: one report, many sources

Guided class activity · apply the ideas from the full SQL lessons.

Module overview & notes →All interactive lectures

Preparing for Lab 9 · After practice, try the team challenge.

Opening your saved work…

Sign in to save across devices → · Download your guest work before switching accounts.

Count qualifying rows and total their values

Step 3 of 8 · Database: Boreal Board Game Café

READ & UNDERSTAND

COUNT(expression) counts non-NULL values. A CASE inside COUNT should return a value only for qualifying rows; an omitted ELSE returns NULL. ELSE 0 is still a non-NULL value and makes the count include the rows you meant to skip.

SUM has a different job. SUM(CASE WHEN condition THEN amount ELSE 0 END) adds the selected amounts and contributes zero for other rows. Ask whether a requested measure is a count of orders or an amount of money before choosing the aggregate.

For an operational prep queue, inspect sessions lasting at least 60 minutes and the total minutes they used. These are different units. A count plus its complementary count should equal the total rows; a sum of minutes should not be compared to a row count.

Worked example
SELECT COUNT(CASE WHEN minutes_played >= 60 THEN 1 END) AS long_sessions, COUNT(CASE WHEN minutes_played < 60 THEN 1 END) AS shorter_sessions, COUNT(*) AS sessions FROM sessions;

The two disjoint counts reconcile to all sessions. A row can satisfy only one of these duration conditions.

The practice totals qualifying minutes, not a boolean flag and not all minutes.

Your question

Return one row with long_sessions and long_minutes for sessions lasting at least 60 minutes. Use COUNT(CASE...) and SUM(CASE...).

Explain your evidence

Why is ELSE 0 appropriate for this SUM and wrong for this COUNT? Name the units of both columns.

Try a query to unlock the worked-answer discussion.

Loading…
Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

CURRENT DATABASE

Quick schema · Boreal Board Game Café

Press Esc or click outside to return to your query. PK = primary key · FK = declared foreign key.

0 of 0 tables

This database has no tables yet. Create a table, then reopen the schema to see its columns.

Control Space also opens names. Enter adds a new line. Control or Command Enter runs your query.

Query results

Predict the result, then run your query.

Predict the result, then run your query.

Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.

Explore this query freely in the playground ↗

Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.