[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Count with condition

Hi,

I have a table below:

TableA

1. TrsNo.
2. TrsDate.
3. TrsAmount.
4. Paid                 (Bit)


I want to count Paid Column if Bit = 0 or Bit = 1

Result.

TrsDate  Total Amount      Paid    Not Paid

Note :
1. Total amount = sum(TrsAmount) for TrsDate Grouping.
2. Paid = count(Paid) for Paid Value =1
3. Paid = count(Paid) for Paid Value =0

How to do it?

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 2
  • 2
1 Solution
 
lwadwellCommented:
Hi emi_sastra,

try something like:

SELECT trsdate, SUM(trsamount) as totalamount, SUM(DECODE WHEN Paid=1 THEN 1 ELSE 0 END) as paid, SUM(DECODE WHEN Paid=0 THEN 1 ELSE 0 END) as notpaid
FROM yourtable
GROUP BY trsdate

lwadwell
0
 
emi_sastraAuthor Commented:
Hi lwadwell,

Where is the DECODE came from?

Thank you.
0
 
lwadwellCommented:
sorry ... I mean CASE

(I was just working on something in Oracle which supports the DECODE)
0
 
emi_sastraAuthor Commented:
Ok.

Thank you very much for your help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now