How to calculate rows count in where statement in sql?

I have two tables in SQL Server:

• order (columns: order_id, payment_id)
• payment (columns: payment_id, is_pay)

I want to get all orders with two more properties:

1. How many rows where is_pay is 1:

    where payment_id = <...> payment.is_pay = 1
   

2. And the count of the rows (without the first filter)

    select count(*) 
    from payment 
    where payment_id = <...>
   

So I wrote this query:

select 
    *, 
    (select count(1) from payment p 
     where p.payment_id = o.payment_id and p.is_pay = 1) as total 
from 
    order o

The problem is how to calculate the rows without the is_pay = 1?

I mean the “some of many”

Answer

First aggregate in payment and then join to order:

SELECT o.*, p.total_pay, p.total
FROM [order] o 
LEFT JOIN (
  SELECT payment_id, SUM(is_pay) total_pay, COUNT(*) total
  FROM payment
  GROUP BY payment_id
) p ON p.payment_id = o.payment_id;

Change LEFT to INNER join if all orders have at least 1 payment.
Also, if is_pay‘s data type is BIT, change SUM(is_pay) to:

SUM(CASE WHEN is_pay = 1 THEN 1 ELSE 0 END)