Pipeline Count by Stage

Intermediate

A pipeline dashboard shows how many deals sit in each stage.

Return each StageName with the number of Opportunities in it, aliased total.

Sample data

Opportunity 6 records

NameStageNameAmountCloseDate
AProspecting10,000today + 30 days
BProspecting20,000today + 30 days
CQualification30,000today + 30 days
DClosed Won40,000today + 30 days
EClosed Won50,000today + 30 days
FClosed Won60,000today + 30 days

Expected result on the sample

StageNametotal
Prospecting2
Qualification1
Closed Won3

Row order does not matter. Only the columns above are compared; extra columns in your SELECT are fine.

Hints
  • COUNT(Id) total gives the count the alias total.
  • Every non-aggregated field in SELECT must be in GROUP BY.

What this tests

GROUP BY with COUNT(Id) does the counting in the database and returns one AggregateResult per stage, which you read in Apex with ar.get('total'). COUNT(Amount) looks similar but only counts rows where Amount is filled in. Aggregate queries also count every row they scan toward the 50,000-row limit, so they are not free on large objects.

SOQL

Run on sample executes your query against the sample data shown on the left and displays the rows it returns. Submit checks it against the sample plus 2 hidden datasets, comparing your rows with the expected rows on each. Queries run in a real Salesforce org; the data is inserted for your run and rolled back afterwards.