Im trying to do a count of particular transactions that have specific conditions and cant seem to make it work.Also in the query I select other columns from other tables

for example: Want to select all transactions

• where transactions.ID_1 = transactions.ID_2,

• where transaction.dir = "outbound" and transactions.status in ("completed", "processing") and do a count on this. like:

    select 
    m.ID
   ,m.Number
   ,t.Status
   ,(column that counts of all transactions with the conditions mentioned above)
   ,p.label
  
   from module m 
   inner join transactions t on t.ID_1 = m.ID
   inner join process p on p.ID = m.ID`
  

Tried with sum and when and if statement but doesn't work

CodePudding user response：

I consider this is a simple count you can use the next script:

SELECT COUNT(transactions.ID_1) 
FROM YOUR_TABLE 
WHERE transactions.ID_1 = transactions.ID_2
AND transaction.dir = "xxx" 
AND transactions.status in ("a", "b")

CodePudding user response：

Seems like you want to GROUP BY. Use a CASEexpression to do conditional aggregation.

select 
  m.ID
 ,m.Number
 ,t.Status
 ,SUM(case when transaction.dir = 'outbound'
            and transactions.status in ('completed', 'processing') then 1
           else 0
      end)
 ,p.label

from module m 
 inner join transactions t on t.ID_1 = m.ID
 inner join process p on p.ID = m.ID
group by m.ID
 ,m.Number
 ,t.Status
  p.label