> ## Documentation Index
> Fetch the complete documentation index at: https://docs.braintrust.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# COUNT(DISTINCT) in GROUP BY returns row count

> [!NOTE]
>
> **Applies to:**
>
> * Plan - Any
> * Deployment - Any
> *
> * Use case - BTQL queries using COUNT(DISTINCT) with GROUP BY for user aggregation or monitoring dashboards

Summary

When using `COUNT(DISTINCT field)` in a BTQL query with `GROUP BY`, the result returns the total row count for each group instead of returning 1 per distinct group, causing apparent duplicate user IDs in logs aggregation queries. This is the current expected behavior of how BTQL processes `COUNT(DISTINCT)` aggregations within `GROUP BY` clauses. Use `count_distinct(field)` function instead of `COUNT(DISTINCT field)`, or restructure the query to avoid `COUNT(DISTINCT)` in `GROUP BY` contexts.

## Resolution Steps

### If you need distinct counts per group

#### Step 1: Replace COUNT(DISTINCT) with count\_distinct()

Use the `count_distinct()` function in your query instead of `COUNT(DISTINCT field)`.

```javascript theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
SELECT
  metadata.user,
  count_distinct(metadata.user) as distinct_users,
  COUNT(*) as total_rows
FROM project_logs('PROJECT_ID', shape => 'spans')
WHERE created > NOW() - INTERVAL 20 day
  AND span_attributes.name = 'streamChat'
  AND metadata.user IS NOT NULL
GROUP BY metadata.user

```

#### Step 2: Verify results on the Query page

Test the query on the Query page to confirm it returns the expected distinct counts.

### If you need time-series distinct counts for monitoring dashboards

#### Step 1: Group by time period and user

Add date grouping to get distinct user counts distributed by day.

```javascript theme={"theme":{"light":"github-light","dark":"github-dark-dimmed"}}
SELECT
  DATE(created) as date,
  metadata.user,
  COUNT(*) as event_count
FROM project_logs('PROJECT_ID', shape => 'spans')
WHERE created > NOW() - INTERVAL 3 day
  AND span_attributes.name = 'streamChat'
  AND metadata.user IS NOT NULL
GROUP BY DATE(created), metadata.user
ORDER BY date DESC, event_count DESC

```

#### Step 2: Use count distinct in dashboard charts

Select **count distinct** as the aggregator when configuring a measure in a [dashboard chart](https://docs.braintrust.dev/observe/dashboards/build-charts.md). This gives you a time-series view of distinct values without a custom SQL query.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.