Single-row query: zero or many rows

SOQL queries · Intermediate · 13 min · Runs in your Salesforce Developer org · Apex Path

Count matches for three Account names: a duplicate name, a solo name, and a missing name. Use `SELECT COUNT()` so you get an Integer back, which never throws for 0 rows. The point is to see why `Account a = [SELECT ...]` crashes on 0 or 2+ rows.

Lesson

Why this matters

Account a = [SELECT Id FROM Account WHERE Name = :key] reads great, but it only works when the query returns exactly one row. If the Name does not exist, Apex throws System.QueryException: List has no rows for assignment. If two Accounts share the Name, Apex throws List has more than 1 row. Real orgs always have duplicates and misses, so this is a crash waiting to happen.

The smallest working example

List<Account> rows = [SELECT Id FROM Account WHERE Name = :key];
if (rows.size() == 1) {
    Account one = rows[0];
}

Assigning into a List<Account> is safe for 0 rows, 1 row and many rows. If you only care about the count, go further:

Integer total = [SELECT COUNT() FROM Account WHERE Name = :key];

COUNT() returns an Integer, so 0 is just 0, not an exception.

Name the parts

**SELECT COUNT()** (no field inside the parens) returns an Integer for the whole query, not a list.
**List<Account>** is the safe shape for "may be zero, one, or many" when you need the rows.
**:key** is a bind variable; the query only reads rows whose Name matches it.

A common wrong version

Account a = [SELECT Id FROM Account WHERE Name = :missingKey];

This throws QueryException because nothing matches. The same line throws again if two Accounts share the Name. The method never gets to return anything.

The fix

For pure counts, use SELECT COUNT():

Integer manyCount = [SELECT COUNT() FROM Account WHERE Name = :manyKey];
Integer soloCount = [SELECT COUNT() FROM Account WHERE Name = :soloKey];
Integer missingCount = [SELECT COUNT() FROM Account WHERE Name = :missingKey];

Each line is safe for 0 rows, 1 row or many rows.

Now you try

The starter seeds two duplicates named <tag>-A, one <tag>-SOLO, and never inserts <tag>-MISSING. Build the three bind keys, run three SELECT COUNT() queries, and print the three labels in order: Many count:, Solo count:, Missing count:.

Learn more

The challenge

Count matches for three tagged Account names and report each case cleanly.

Examples:

Name <tag>-A is inserted twice → Many count: 2
Name <tag>-SOLO is inserted once → Solo count: 1
Name <tag>-MISSING is never inserted → Missing count: 0

Requirements:

Uses COUNT() rather than List should be present: three SELECT COUNT() FROM Account WHERE Name = :... queries.
Never assigns into a bare sObject should be present: do not write Account only = [SELECT ...] on any lookup.
Many count: 2 should be printed for the duplicate name.
Solo count: 1 should be printed for the solo name.
Missing count: 0 should be printed for the missing name.

Starter code

String tag = 'AS-Q316-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
List<Account> seeded = new List<Account>{
    new Account(Name = tag + '-A'),
    new Account(Name = tag + '-A'),
    new Account(Name = tag + '-SOLO')
};
insert seeded;

System.debug('Many count: 0');
System.debug('Solo count: 0');
System.debug('Missing count: 0');

Related drills

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Search names with SOSL · WITH USER_MODE versus system mode ›