MSQL Subquery

Find customers whose payments are above a certain value :

SELECT
      customer_number,
      check_number,
      ammount
FROM
      payments
 WHERE
       amount >(SELECT
                  AVG(amount)
               FROM
                    payments);

Comments

[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)