a join or other method to retrieve when one query is null and other have result

Question

I have this sql query which is combining of two query,for a specific user in a unique matter (named title) first return the count and sum of scores where they are negative and second return sum and count of positive scores:

 SELECT *
FROM (
    SELECT title,
        count(*) cnt_neg,
        sum(score) sum_neg
    FROM scores_ofexpert
    WHERE user_id = "30"
        AND title = "137"
        AND score < 0
    GROUP BY title
    ) neg,
    (
        SELECT count(*) cnt_pos,
            sum(score) sum_pos
        FROM scores_ofexpert
        WHERE user_id = "30"
            AND title = "137"
            AND score >= 0
        GROUP BY title
        ) pos

the problem is this: when both return values it work good, but when one return null, both shows null either if i run one it work true. for example if a person with id of 30 have 2 positive score in title1 and no negative score on title1 the query return null for neg_count and neg_sum and pos_sum and pos_count. but if i run only the part which work on positive it work true...

I tried the right join: it work only when second query has result and tried left join and it return value only when first have result. any better way to do this?

Answer 1

I found this answer. It worked well for me:

    SELECT
     SUM(CASE WHEN s.score < 0 THEN s.score ELSE 0 END) n_sum,COUNT(CASE WHEN s.score < 0 THEN s.score END) n_count,
     SUM(CASE WHEN s.score >= 0 THEN s.score ELSE 0 END) p_sum,COUNT(CASE WHEN s.score >= 0 THEN s.score END)  p_count,
     SUM(s.score) `sum`
     FROM scores_ofexpert s
         WHERE s.user_id = '30' and title='135'
    GROUP BY title