title: SQL - How to count number of distinct values (payments), after sum of rows where they have another column value (Due Date) in common
body: <p>My 'deals_payments' table is:</p>
  
  <pre><code>Due Date    Payment     ID
  1-Mar-19    1,000.00    123
  1-Apr-19    1,000.00    123
  1-May-19    1,000.00    123
  1-Jun-19    1,000.00    123
  1-Jul-19    1,000.00    123
  1-Aug-19    1,000.00    123
  1-Jun-19    500.00      456
  1-Jul-19    500.00      456
  1-Aug-19    500.00      456
  </code></pre>
  
  <p>I have the SQL code:</p>
  
  <pre><code>select 
     count(*), payment
  
  from (select deals_payments.*,
               (row_number() over (order by due_date) -
                row_number() over (partition by payment order by due_date)
               ) as grp
        from deals_payments
         where id = 123
             ) deals_payments
  group by grp, payment
  order by grp
  </code></pre>
  
  <p>which gives me what I want - the number of payments on each distinct amount - (here I only asked for ID 123):</p>
  
  <pre><code>COUNT(*)    PAYMENT
  6           1000.00
  </code></pre>
  
  <p>But now I need the sum of payments of the two ID's (123 and 456), where the due dates are the same, and count the number of payments on each distinct amount, as:</p>
  
  <pre><code>COUNT(*)    PAYMENT
  3           1000.00
  3           1500.00
  </code></pre>
  
  <p>I tried the below but it gives me the 'missing right parenthesis' error. What is wrong??</p>
  
  <pre><code>    select 
        count(*), 
        (select 
            sum(total) total
         from (select distinct 
                  due_date,
                  (select 
                     sum(payment) 
                  from deals_payments 
                  where  (due_date = a.due_date)) as total
               from deals_payments a 
               where a.id in (123, 456) 
               and payment &gt; 0)
  
          group by due_date
          order by due_date)  b
  
      from (select deals_payments.*,
                       (row_number() over (order by due_date) -
                                row_number() over (partition by payment order by due_date)
                               ) as grp
                        from deals_payments
                         where id = 123
                             ) deals_payments
                  group by grp, payment
                  order by grp
  </code></pre>