CODEWITHSUNDEEP
sqlselectcountcasesybase
Matteo NNZ
Matteo NNZ

Reputation: 12665

Perform two counts (with different conditions) within the same SQL request

I have to run the following two queries on a Sybase engine:

SELECT COUNT(*) AS BTB_YES FROM FXMM_EVT_DBF WHERE M__INTID_ = '1.511' AND M_ISHEDGE = 1
SELECT COUNT(*) AS BTB_NO FROM FXMM_EVT_DBF WHERE M__INTID_ = '1.511' AND M_ISHEDGE = 0

Since I basically select the same set of information, i.e. : 

SELECT M_ISHEDGE
FROM FXMM_EVT_DBF
WHERE M__INTID_ = '1.511'

and then I count on this column twice with two separate conditions, I kinda feel there is a way to have the result on the same single query, which would be great since the above queries must be run on several databases.

However, I'm being unable to get a joint result, probably because I'm not very good at SQL. My try was: 

SELECT M_ISHEDGE
FROM FXMM_EVT_DBF WHERE M__INTID_ = '1.511'
CASE WHEN M_ISHEDGE = 1
    then count(*)
    else 0
END AS BTB_YES
CASE WHEN M_ISHEDGE = 0
    then count(*)
    else 0
END AS BTB_NO

but the above fails with the following error: 

An error occurred when executing the SQL command:
SELECT M_ISHEDGE
FROM FXMM_EVT_DBF WHERE M__INTID_ = '1.511'
CASE WHEN M_ISHEDGE = 1
    then count(*)
    else 0
END AS BTB_Y...
Incorrect syntax near the keyword 'CASE'. [SQL State=ZZZZZ, DB Errorcode=156] 

Execution time: 0s

1 statement(s) failed.

and the SQL editor does not seem to recognize the keyword THEN, although it is part of the documentation, so probably because of some syntax mistake.

Upvotes: 1

Views: 291

Answers (2)

jpw
jpw

Reputation: 44891

You'll want to move the case expression inside the count function like this:

SELECT 
    COUNT(CASE WHEN M_ISHEDGE = 1 then m_ishedge end) AS BTB_YES,
    COUNT(CASE WHEN M_ISHEDGE = 0 then m_ishedge end) AS BTB_NO
FROM 
    FXMM_EVT_DBF 
WHERE 
    M__INTID_ = '1.511'

Since the case expressions returns null if no when condition matches you can omit the else part (and count only counts non-null values).

Upvotes: 1

Mureinik
Mureinik

Reputation: 311458

You had the right idea with the case expression, but, as you noticed, the wrong syntax. The idea here is that count omits nulls, so you'd want to apply the count function itself to the case expression:

SELECT COUNT (CASE M_ISHEDGE WHEN 1 THEN 1 ELSE NULL END) AS BTB_YES,
       COUNT (CASE M_ISHEDGE WHEN 0 THEN 1 ELSE NULL END) AS BTB_NO
FROM   FXMM_EVT_DBF 
WHERE  M__INTID_ = '1.511'

Upvotes: 1

Related Questions