1

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!

Craig
  • 364
  • 1
  • 3
  • 25
  • what do you mean by `The business rules are that a single payment may not exceed 10000`? – Vamsi Prabhala Jun 28 '17 at 23:52
  • Originally I just had two queries, one did a count of the paymentamounts => 10000 and another query that did a count of payments < 10000. Then I discovered the business rules indicating that a single payment cannot exceed 10000. So now I need to get a count of the sum of payments that are either below 10k or 10k + since I am not just looking at a single payment, I am looking across multiple. – Craig Jun 28 '17 at 23:59

2 Answers2

2

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
Ahmed Negm
  • 865
  • 1
  • 11
  • 30
  • 1
    The WITH clause is a way of writing a subquery. It is just a different way of writing it. And it is simply not true that subqueries are bad performance. You _may_ have a performance impact if a subquery is executed in a nested loop but that is clearly not the case here. – fhossfel Jun 29 '17 at 01:22
  • Thanks for your comment, I was just talking generally. I know that both CTE and SubQuery are, in theory, the same as both provide the same info to the query optimizer. But, I prefer CTE as it could be easily identified and calculated once, then you can reuse. – Ahmed Negm Jun 29 '17 at 01:38
  • This provided me with what I was looking for. Though I was a little vague with my requirements because I already had some of the query built I just used this logic and separated it into two queries. Thank you. – Craig Jun 29 '17 at 13:49
1

You can get the totals per account using SUM and GROUP BY...

SELECT AccountNo, SUM(Payment) AS TotPay
FROM payments
GROUP BY AccountNo

You can use that result to count the number over 10000

SELECT COUNT(*)
FROM (
    SELECT AccountNo, SUM(Payment) AS TotPay
    FROM payments
    GROUP BY AccountNo
)
WHERE TotPay>10000

You can get the the number over and the number under in a single query if you want but that's a but more complicated:

SELECT
   COUNT(CASE WHEN TotPay<=10000 THEN 1 END) AS Below10K,
   COUNT(CASE WHEN TotPay> 10000 THEN 1 END) AS Above10K
FROM (
    SELECT AccountNo, SUM(Payment) AS TotPay
    FROM payments
    GROUP BY AccountNo
)
SQL Hacks
  • 1,322
  • 1
  • 10
  • 15
  • 1
    SUM works fine, but I suppose COUNT is clearer. ELSE 0 is redundant as the default NULL values delivers what is required. – SQL Hacks Jun 29 '17 at 00:10
  • As I am sure this would have worked, I got errors in my query where it said Totpay was not a column. Maybe I had to use an alias or something? – Craig Jun 29 '17 at 13:48