SQL Query to get sums among multiple payments which are greater than or less than 10k

Question

I am trying to write a query to get sums of payments from accounts for a month. I have been able to get it for the most part but I have hit a road block. My challenge is that I need a count of the amount of payments that are either < 10000 or => 10000. The business rules are that a single payment may not exceed 10000 but there can be multiple payments made that can total more than 10000. As a simple mock database it might look like

ID | AccountNo | Payment
1  | 1         | 5000
2  | 1         | 6000
3  | 2         | 5000
4  | 3         | 9000
5  | 3         | 5000

So the results I would expect would be something like

NumberOfPaymentsBelow10K | NumberOfPayments10K+
1                        | 2

I would like to avoid doing a function or stored procedure and would prefer a sub query.

Any help with this query would be greatly appreciated!

Answer 1

I suggest avoiding sub-queries as much as possible because it hits the performance, specially if you have a huge amount of data, so, you can use something like Common Table Expression instead. You can do the same by using:

;WITH CTE
AS
(
    SELECT AccountNo, SUM(Payment) AS TotalPayment
    FROM Payments
    GROUP BY AccountNo
)

SELECT
    SUM(CASE WHEN TotalPayment < 10000 THEN 1 ELSE 0 END) AS 'NumberOfPaymentsBelow10K',
    SUM(CASE WHEN TotalPayment >= 10000 THEN 1 ELSE 0 END) AS 'NumberOfPayments10K+'
FROM CTE