Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm using the following SQL (with a union to two similar queries):

SELECT
    distinct a.source,
    a.p_id,
    a.name,
    b.prod_count,
    b.prod_amt,
    'Def' as prod_type
FROM
    dwh.attribution_product_count a
        LEFT OUTER JOIN
        (
SELECT
    distinct source,
    p_id,
    name,
    sum(acct_count) as prod_count,
    sum(acct_amt) as prod_amt
FROM
    dwh.prod_count
WHERE
    month = 3 AND
    default_banner_flag = 0 AND
    loan_flag = 3
GROUP BY
    source,
    name,
    p_id ) as b
        ON
        a.p_id = b.p_id
UNION
SELECT
    distinct a.source,
    a.p_id,
    a.name,
    b.prod_count,
    b.prod_amt,
    'Other' as prod_type
FROM
    dwh.attribution_product_count a
        LEFT OUTER JOIN
        (
SELECT
    distinct source,
    p_id,
    name,
    sum(acct_count) as prod_count,
    sum(acct_amt) as prod_amt
FROM
    dwh.prod_count
WHERE
    month = 3 AND
    default_banner_flag = 1 AND
    loan_flag = 3
GROUP BY
    source,
    name,
    p_id
ORDER BY
    name ) as b
        ON
        a.p_id = b.p_id

The output I'm getting looks like this:

enter image description here Essentially since FakeName #2 has one row showing actual numbers (not null), I ONLY want FakeName #2 to show up. This means I also want the null row for FakeName #2. But, since FakeName #1 and #3 have 2 null rows, I don't need them to show. What type of SQL command (or edit to my query) can accomplish this?

share|improve this question

1 Answer

up vote 1 down vote accepted

Firstly, if I read your query correctly, you can eliminate the need for a UNION by using CASE and IN. You also have a couple of bogus DISTINCTs in there (since you're using GROUP BY anyway). That gives:

SELECT DISTINCT
    a.source,
    a.p_id,
    a.name,
    b.prod_count,
    b.prod_amt,
    Case When default_banner_flag = 0 Then 'Def' Else 'Other' End as prod_type
FROM
    dwh.attribution_product_count a
LEFT OUTER JOIN
    (
    SELECT
        source,
        p_id,
        name,
        default_banner_flag,
        sum(acct_count) as prod_count,
        sum(acct_amt) as prod_amt
    FROM
        dwh.prod_count
    WHERE
        month = 3 AND
        default_banner_flag in (0, 1) AND
        loan_flag = 3
    GROUP BY
        source,
        name,
        p_id,
        default_banner_flag
) as b
ON
a.p_id = b.p_id

However, what you actually want is information about those p_ids which have at least one row in dwh.prod_count, so I think you can change your whole query around to use that as the sub-select:

SELECT
    a.source,
    a.p_id,
    a.name,
    sum(acct_count) as prod_count,
    sum(acct_amt) as prod_amt,
    Case When default_banner_flag = 0 Then 'Def' Else 'Other' End as prod_type
FROM
    dwh.attribution_product_count a
LEFT OUTER JOIN
    dwh.prod_count b
    On a.p_id = b.p_id
INNER JOIN
    (
    SELECT DISTINCT
        p_id
    FROM
        dwh.prod_count
    WHERE
        month = 3 AND
        default_banner_flag in (0, 1) AND
        loan_flag = 3
    ) as c
    ON a.p_id = c.p_id
WHERE
    month = 3 AND
    default_banner_flag in (0, 1) AND
    loan_flag = 3

(You could also rewrite this as a WHERE p_id IN ( sub-select ) or with a little fiddling WHERE EXISTS ( ... ), but this seemed the easiest version to demonstrate.)

Note that I haven't actually tested any of these queries, but I think they're logically sound.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.