How to have conditional group by in sql?

Question

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

If it's a SQLServer question, please add the "sql-server" tag to your question, thanks. — Jérôme Radix, Jan 24 '15 at 07:37

score 2 · Accepted Answer · answered Jan 24 '15 at 06:37

2

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

answered Jan 24 '15 at 06:37

Pரதீப்

91,748
19
131
172

Count can't use a conditon. it counts all that is not `null`, no matter if `0` or `1` – juergen d Jan 24 '15 at 06:40
@juergend - really!! thats not true check here http://stackoverflow.com/questions/1288058/conditional-count-on-a-field – Pரதீப் Jan 24 '15 at 06:43
I just checked that. You're right. So am I :) You can use a condition but you must make sure that the `false` part is `null` – juergen d Jan 24 '15 at 06:44
@juergend Since we are not using `else` part is `case` statement all the other values will be `NULL` and those won't be counted – Pரதீப் Jan 24 '15 at 06:48

score 2 · Answer 2 · answered Jan 24 '15 at 06:39

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

How to have conditional group by in sql?

2 Answers2