CODEWITHSUNDEEP
sql
David Nguyen
David Nguyen

Reputation: 8508

UNION ALL with unknown column value

Hello I have a working SELECT that uses UNION ALL to return 0 values, however I'm not sure how to add another column to the UNION that has an unknown value:

SELECT status, type, company
    ROW_NUMBER() OVER (PARTITION BY title ORDER BY id) As RowNumber
FROM table
WHERE type IN ('type1', 'type2', 'type3') AND
    status IN ('status1', 'status2', 'status3')

/* issue starts here */
UNION ALL SELECT 'status1', 'type1', null, 1
UNION ALL SELECT 'status2', 'type1', null, 1
UNION ALL SELECT 'status3', 'type1', null, 1

/*                                    ^ unknown value */

/* ... repeat for all status * type combinations */

RowNumber is used to find the first occurrence of a title in the parent SELECT(not shown).

The expected end result is placed in another query in the FROM clause to do a count:

--------------------------------------
| status   | type  | company | count |
--------------------------------------
| status1  | type1 | abc     | 1     |
| status2  | type1 | abc     | 24    |
| status3  | type1 | abc     | 0     |
--------------------------------------

As you can see I tried NULL for the company in the UNION ALL, this doesn't work is there a way to "use" the values found in the "company" column?

Thanks.

Upvotes: 1

Views: 1954

Answers (2)

Michael Rice
Michael Rice

Reputation: 1183

I this this may be what you're looking for

SELECT
    status
    , type
    , company
    , SUM(Counter) as count
FROM
(
    SELECT
        status
        , type
        , company
        ROW_NUMBER() OVER (PARTITION BY title ORDER BY id) As RowNumber
        , 1 AS Counter
    FROM table
    WHERE
        type IN ('type1', 'type2', 'type3')
        AND status IN ('status1', 'status2', 'status3')

    UNION ALL SELECT 'status1', 'type1', null, 1, 0
    UNION ALL SELECT 'status2', 'type1', null, 1, 0
    UNION ALL SELECT 'status3', 'type1', null, 1, 0
) t1
WHERE RowNumber = 1

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

The first thing I see is that your main select returns four columns while the unions return six columns. All union statements must return the same number of columns as the initial select.

If you want to select a company unique to a user, do an actual select:

UNION ALL
SELECT 'status1', 'type1', users_company, 1
from users_companies
where user = current_user

Upvotes: 0

Related Questions