Combining the results of two Select statments

Transact-SQL
1

Hi All,

I have two select statements . I want to combine the results of these two select statements.
I tried UNION and I am getting this error "Conversion failed when converting the varchar value 'n' to data type int".
Is there any way I can combine these two statements.
Below are the two statements:

  1. SELECT Descr,FilterA,FilterB,FilterC,CreateDate from X JOIN Y on Y_ID = X_ID WHERE Label IN ('111','222','333')
    '
  2. SELECT Label, COUNT(Label), FROM X JOIN Y on Y_ID = X_ID WHERE CreateDate >= DATEADD(day, -7, GETDATE()) AND Label IN ('111','222','333')
    GROUP BY Label

Thank You

2

Union requires that all datatypes between the two selects are compatible. ALso, the number of columns must match. (They don't in your example). I suspect that FilterA is not an integer. To fix the union you could, in the second select, write

SELECT Label, CAST(COUNT(Label) as varchar(10)) , null, null, null
3

Hi gbritton. Thanks for the reply.

Yes as you have mentioned FilterA is not an integer.

I have made the following changes to the statements

SELECT Descr, FilterA, FilterB, FilterC, CreateDate , null, null from X JOIN Y on Y_ID = X_ID WHERE Label IN ('111','222','333')
UNION ALL
SELECT Label, CAST(COUNT(Label ) as varchar(10)) AS N ,null,null,null,null FROM X JOIN Y on Y_ID = X_ID WHERE CreateDate >= DATEADD(day, -7, GETDATE()) AND Label IN ('111','222','333')
GROUP BY Label

When I do this it is returning NULL for both the columns Label and N (which is the count of label)

Thank You

4

Something like?

SELECT Descr, FilterA AS N, FilterB, FilterC, CreateDate , null AS XYZ, null AS ZYX
from X JOIN Y on Y_ID = X_ID 
WHERE Label IN ('111','222','333')
UNION ALL
SELECT Label, CAST(COUNT(Label ) as varchar(10)) AS N ,null,null,null,null,null 
FROM X JOIN Y on Y_ID = X_ID 
WHERE CreateDate >= DATEADD(day, -7, GETDATE()) AND Label IN ('111','222','333')
GROUP BY Label

By-the-way please use the </> to format your code.

5

@ djj55

I have used the exact same query and it is returning null values for both columns Label and N

6

Then there are no rows satisfying the where clause.

7

Assuming that the WHERE clause is correctly not finding any rows? then to display Zero instead of Null change

CAST(COUNT(Label ) as varchar(10))

to

CAST(COALESCE(COUNT(Label), 0) as varchar(10))
8

@Kristen

Thanks for the reply.
I have tried your query but it still returns NULL for both Label and N

9

Please post the full query you have built so far. I have the idea that something is missing. Please enclose your query in </>

10

SELECT Descr, FilterA AS N, FilterB, FilterC, CreateDate , null AS XYZ, null AS ZYX
from X JOIN Y on Y_ID = X_ID
WHERE Label IN ('111','222','333')
UNION ALL
SELECT Label, CAST(COUNT(Label ) as varchar(10)) AS N ,null,null,null,null,null
FROM X JOIN Y on Y_ID = X_ID
WHERE CreateDate >= DATEADD(day, -7, GETDATE()) AND Label IN ('111','222','333')
GROUP BY Label

11

This is what we mean by formatted:

SELECT Descr
      , FilterA AS N
      , FilterB
      , FilterC
      , CreateDate
      , NULL AS XYZ
      , NULL AS ZYX
FROM X
INNER JOIN Y
      ON Y_ID = X_ID
WHERE Label IN ('111', '222', '333')

UNION ALL

SELECT Label
      , CAST(COUNT(Label) AS VARCHAR(10)) AS N
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
FROM X
INNER JOIN Y
      ON Y_ID = X_ID
WHERE CreateDate > DATEADD(day, - 7, GETDATE())
      AND Label IN ('111', '222', '333')
GROUP BY Label
1 Like
12

If your query is returning nulls, then that's what SQL found.

Run each part of the query by itself to confirm