CODEWITHSUNDEEP
sqlcountdb2
user982998
user982998

Reputation: 69

SQL DB2 count based on previous case

do someone know a solution for a count based on previous case statement ?

here is what i mean in a query, does not work acutally

SELECT                              
    CASE 
        WHEN field1 IN ('a','b','c') THEN 'MATCH1'
        WHEN field2 IN ('d','e','f') THEN 'MATCH2'
        ELSE 'NOMATCH' END AS MATCHFIELD,
    (SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNT
FROM TABLE1

i am on db2 v9.X. i have tryed HAVING but could not find a viable solution

regards

Upvotes: 0

Views: 192

Answers (3)

Esperento57
Esperento57

Reputation: 17462

try this :

with tmp as (
SELECT  CASE 
        WHEN field1 IN ('a','b','c') THEN 'MATCH1'
        WHEN field2 IN ('d','e','f') THEN 'MATCH2'
        ELSE 'NOMATCH' END AS MATCHFIELD
FROM TABLE1
)

select tmp.*,  (SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNT
from tmp

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I would use JOIN and GROUP BY

SELECT MATCHFIELD, COUNT(*)
FROM TABLE2
JOIN
(
    SELECT                              
        CASE 
            WHEN field1 IN ('a','b','c') THEN 'MATCH1'
            WHEN field2 IN ('d','e','f') THEN 'MATCH2'
            ELSE 'NOMATCH' END AS MATCHFIELD
    FROM TABLE1  
) t ON field3=t.MATCHFIELD
GROUP BY MATCHFIELD

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Wrap the SELECT with the case expression up in a derived table:

select MATCHFIELD,
    (SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNTSELECT                              
from
(
    select
          CASE 
               WHEN field1 IN ('a','b','c') THEN 'MATCH1'
               WHEN field2 IN ('d','e','f') THEN 'MATCH2'
               ELSE 'NOMATCH'
          END AS MATCHFIELD
    FROM TABLE1
) dt

Upvotes: 3

Related Questions