Order by Different Aggregate

Order rows by one aggregate while displaying a different one. This shows occupations ordered by total income but displays average income in the cells.

Interactive Example

Try It

TABLE
  ROWS occupation DESC@income.sum
  COLS education * income.mean
;

Dataset:samplesoccupation, education, gender, income

Try Variations

Query Breakdown

• TABLE - Declares a crosstab table statement
• ROWS occupation DESC@income.sum - All occupations ordered by sum of income (descending)
  • DESC - Descending order (highest values first)
  • @income.sum - Order by sum of income, not alphabetically
• COLS education * income.mean - Column dimension showing average income
  • The displayed measure (income.mean) differs from the ordering measure (income.sum)
• income.mean - Measure: average income for each cell

The ordering aggregate (@income.sum) is independent of the displayed measure (income.mean). This is useful when you want to rank by total volume but show per-capita or average values.

Related Examples

• Order by Value - Order and display the same aggregate
• Order Ascending by Value - Ascending value-based ordering
• Top N by Value - Combine different aggregates with limits