CODEWITHSUNDEEP
sql-server
Elahe
Elahe

Reputation: 1399

How to have conditional group by in sql?

I have a table with this columns:

tblTransaction 
(
    Transaction_ID, 
    Transaction_RequestTransactionCode,
    Transaction_MobileErrorCode
)

I want to have the count of EVERY transactions, that it's MobileErrorCode is 0.

so my query is like this:

SELECT        Transaction_RequestTransactionCode, COUNT(Transaction_ID) AS _Count
FROM          tblTransaction
WHERE        (Transaction_MobileErrorCode = '0')
GROUP BY Transaction_RequestTransactionCode

the result is this:

enter image description here

It dosent have any error, but its not my result. when one transaction code dose have the condition (Transaction_MobileErrorCode = '0'), it dosent appear in result with _Count = 0.

I mean this result, with last raw:

enter image description here

Upvotes: 1

Views: 117

Answers (2)

juergen d
juergen d

Reputation: 204746

Use sum to sum up the condition you want to count

SELECT        Transaction_RequestTransactionCode, 
              SUM(case when Transaction_MobileErrorCode = '0' then 1 else 0 end) AS _Count
FROM          tblTransaction
GROUP BY Transaction_RequestTransactionCode

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

To perform a conditional aggregate move the condition from where clause to Count using case statement aggregate

SELECT Transaction_RequestTransactionCode,
       Count(CASE
               WHEN Transaction_MobileErrorCode = '0' THEN Transaction_ID
             END) AS _Count
FROM   tblTransaction
GROUP  BY Transaction_RequestTransactionCode

Upvotes: 2

Related Questions