A data engineer writes the following Spark SQL to query a Delta table:

```sql
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'complete'
GROUP BY customer_id
HAVING SUM(amount) 1000
ORDER BY total DESC
```

What does the `HAVING` clause do in this query?

Question

A data engineer writes the following Spark SQL to query a Delta table:

```sql
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'complete'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC
```

What does the `HAVING` clause do in this query?

Accepted Answer

B. Filters out customers whose total aggregated amount is 1000 or less, after the GROUP BY aggregation. In ANSI SQL (which Databricks SQL follows), the `HAVING` clause filters groups after the `GROUP BY` aggregation has been applied. `WHERE` filters rows before aggregation. In this query, `HAVING SUM(amount) > 1000` removes any `customer_id` groups whose total is not greater than 1000. Sorting is done by `ORDER BY`, not `HAVING`. `WHERE amount > 1000` would filter individual rows before grouping.

A data engineer writes the following Spark SQL to query a Delta table:\n\n
`\nSELECT customer_id, SUM(amount) AS total\nFROM orders\nWHERE status = 'complete'\nGROUP BY customer_id\nHAVING SUM(amount) > 1000\nORDER BY total DESC\n`
\n\nWhat does the `HAVING` clause do in this query?

Related Questions

Discussion

A data engineer writes the following Spark SQL to query a Delta table:\n\n\nSELECT customer_id, SUM(amount) AS total\nFROM orders\nWHERE status = 'complete'\nGROUP BY customer_id\nHAVING SUM(amount) > 1000\nORDER BY total DESC\n\n\nWhat does the HAVING clause do in this query?

Related Questions

Discussion

A data engineer writes the following Spark SQL to query a Delta table:\n\n
`\nSELECT customer_id, SUM(amount) AS total\nFROM orders\nWHERE status = 'complete'\nGROUP BY customer_id\nHAVING SUM(amount) > 1000\nORDER BY total DESC\n`
\n\nWhat does the `HAVING` clause do in this query?