Multiple count in sql for different

Question

I have a doubt about multiple SQL queries.(annidate) I m using standard RDBMS system.

I wish to sum with three different conditions, one column.

Ad example If I have users with bills, and I want to make the sum of the bills paid and those to be paid and the debt respectively (which is the difference between paid and unpaid bills).

my database should be

user | bills | date payments |
1      100     12/01/2020
1      100     NULL
2      200     NULL
1      200     12/02/2020
2      100     12/03/2020

My output should be

   user | payed bills | unpayed bills | debit
   1      100           300             200
   2      200           100             100

my idea should be count a bill with values more than 0 (because the user need to pay) and count a bills that he already payed with date "null".

so i tried to do this in SQL

select count(bills)
from...
where bills >0 and select ( count(bills) from... where data = "null")

But looking this (SQL: Multiple count statements with different criteria)
I discover the "case" state. Can work in my condition? Someone can give me an easy example of a multiple counts of the query ?

Sample data and desired results would make it easier to follow what you are trying to describe. — Gordon Linoff, Aug 19 '20 at 11:03
[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Aug 19 '20 at 11:52
@theantomc . . . don't understand the debit. Shouldn't one value be negative? — Gordon Linoff, Aug 19 '20 at 14:20
@GordonLinoff Isn't relevant, because should represent a value to pay. So positive value is more clear (for me) — theantomc, Aug 19 '20 at 14:32

Olga Romantsova · Accepted Answer · 2020-08-19T15:07:11.640

2

You can calculate Sum and Count payed_bills and unpayed_bills using IIF:

      Select user, 
      sum(case when date payments is null then bills else 0 end)) sum_paid_bill, 
      sum(case when date payments is not null then bills else 0 end)) sum_unpaid_bills,    
      Count(case when date payments is null then 1 else Null end)) count_paid_bill,    
      Count(case when date payments is not null then 1 else Null end)) count_unpaid_bills
      From Table
     Group by user

edited Aug 19 '20 at 15:07

answered Aug 19 '20 at 11:33

Olga Romantsova

1,096
1
6
8

This is SQL server? or can i use in standard SQL? – theantomc Aug 19 '20 at 14:02
I have changed to case, because you using RDBMS system.Please,check it – Olga Romantsova Aug 19 '20 at 14:16
please,check the answer) – Olga Romantsova Aug 19 '20 at 14:35
I have just edited query in Count statements. Please, try it) – Olga Romantsova Aug 19 '20 at 15:07

Gordon Linoff · Answer 2 · 2020-08-19T14:37:44.203

1

You are describing something like this:

select count(bills) as numbills,
       sum(case when data is null then 1 else 0 end) as numbills_nulldate,
       sum(case when data is null then bills end) as sumbills_nulldate
from . . .

That is, the conditions are in the case expression, rather than in the where clause.

EDIT:

For your specific result set:

select user,
       sum(case when date_payment is not null then bill else 0 end) as paid_amount,
       sum(case when date_payment is null then bill else 0 end) as unpaid_amount,
       sum(case when date_payment is not null then bill else - bill end) as debit
from t
group by user;

edited Aug 19 '20 at 14:37

answered Aug 19 '20 at 11:03

Gordon Linoff

1,242,037
58
646
786

2 times paid amount is refuse or I lost something? – theantomc Aug 19 '20 at 14:34
1

@theantomc . . . That was a typo. – Gordon Linoff Aug 19 '20 at 14:37

score 0 · Answer 3 · edited Aug 19 '20 at 14:23

your query should be like this

Select user, 
sum(case when date payments is not  null then bills else 0 end) as paid_bills,
sum(case when date payments is null then bills else 0 end) as unpaid_bills,
isnull(sum(case when date payments is not  null then bills else 0 end),0)> isnull(sum(case when date payments is null then bills else 0 end),0) then
isnull(sum(case when date payments is not  null then bills else 0 end),0) - isnull(sum(case when date payments is null then bills else 0 end),0)
else
isnull(sum(case when date payments is null then bills else 0 end),0) - isnull(sum(case when date payments is not null then bills else 0 end),0) as Diff
      From table1
     Group by user

Multiple count in sql for different

3 Answers3