CODEWITHSUNDEEP
sqlsql-serverrelational-database
Kristijan
Kristijan

Reputation: 195

Count all and count the records that meet a clause in a single query

I have a table that looks like this:

ID | NetAmount | PaymentAmount
1    2.99        1.99
2    2.99        2.99

I wish to count all the records in the table and then divide that number with the count of records where NetAmount-PaymentAmount > 0. How can I achieve this?

(The result in this case would be 1/2 => 0.5)

Upvotes: 1

Views: 39

Answers (3)

Santosh Jadi
Santosh Jadi

Reputation: 1527

Using IIF condition:

SELECT AVG(IIF(NetAmount > PaymentAmount, 1.0, 0.0)) as Result
FROM tableName;

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272296

You can use conditional aggregation:

SELECT 1.0 *
       COUNT(*) /
       COUNT(CASE WHEN NetAmount > PaymentAmount THEN 1 END)
FROM yourdata

The 1.0 * part will ensure that you get a decimal result with 1 digit after 0.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The simplest way uses avg():

select avg(case when netamount > paymentamount then 1.0 else 0.0 end) as ratio
from t;

Upvotes: 1

Related Questions