SQL How to add OR to sum(CASE from 2 different columns to 1

I have a SQL code which sums datas to 2 different columns:

 sum(CASE WHEN u.data LIKE 'first%' THEN 1 ELSE NULL END) AS first_test,
 sum(CASE WHEN u.data LIKE 'second%' THEN 1 ELSE NULL END) AS second_test

which adds 2 columns like this:

first_test   second_test
-------------------------
13042           676
11950           798
105136          8745
17516           776

I want to achieve sum of those 2 to 1 column which contains both of datas 'first%' and 'second%'. I tried something like this:

 sum(CASE WHEN u.data LIKE 'first%' OR 'second%' THEN 1 ELSE NULL END) AS total

to have 1 column with sum of first_test and second_test which is:

total
-------
13718
12748
113881
18292

>Solution :

Simply OR the first and second LIKE conditions:

sum(CASE WHEN u.data LIKE 'first%' OR u.data LIKE 'second%' THEN 1 ELSE 0 END) AS total

(Note that I’ve changed from ELSE NULL to ELSE 0, to avoid the Null values eliminated in set function warning.)

Leave a Reply