DEV Community

Discussion on: MSQL Subquery

aarone4 profile image
Aaron Reese

If this is a question looking for an improved solution it is more suited to stack overflow or sqlservercentral. The query does not return the data in the original request because you will have multiple occurrences of the customer id. Either drop the check_number and amount from the select and use SELECT DISTINCT to only return one occuence of each customer, or wrap the query in a Common Table Expression and do the select distinct on that instead. WTH CTE AS ( original query ) SELECT DISTINCT customer_id FROM CTE.
You don't need the sub query in the where clause, you can just do Where amount > AVG(amount)
If you wanted all checks over the customers average then the where clause would be
WHERE amount > AVG(amount) OVER (PARTITION BY customer_id)