CODEWITHSUNDEEP
sqlsql-serverunion
Esha
Esha

Reputation: 389

Union Query not displaying records for second query

I have the following SQL query:

select (case when len(GroupName) = 0 then 'Unknown' else GroupName end) as GroupName 
,PaperId,Color,Duplex, sum(Page_Printed) As A3PagesPrinted, sum(Cost) as A3TotalCost 
from printstat where paperid = 8 and color = 0 and duplex = 0 
and Date_Print >= '2013-01-01' and Date_Print < '2013-10-21'
group by GroupName, PaperId, Color, Duplex

union all

select (case when len(GroupName) = 0 then 'Unknown' else GroupName end) as GroupName
,PaperId,Color,Duplex, sum(Page_Printed) As A3DuplexPagesPrinted, 
sum(Cost) as A3DuplexTotalCost from printstat where paperid = 8 and color = 0 
and duplex = 1 and Date_Print >= '2013-01-01' and Date_Print < '2013-10-21'
group by GroupName, PaperId, Color, Duplex

Now both the queries return values when run individually. But when I execute them together my record for the second query is displayed A3DuplexPagesPrinted and A3DuplexTotalCost.

Why is that?

Upvotes: 0

Views: 1273

Answers (1)

lc.
lc.

Reputation: 116498

A UNION query will take the column names from the first part of the query, and fill all subsequent parts into those columns in the same order, regardless of names or aliases.

Therefore your fifth and sixth column from the second part (you give them aliases A3DuplexPagesPrinted and A3DuplexTotalCost) will be filled into the fifth and sixth columns of the result and named A3PagesPrinted and A3TotalCost (the aliases from the first SELECT clause).

If you want to differentiate the columns in the two queries, you will have to specify all columns for each part of the query (note the NULLs below):

select case when len(GroupName) = 0 then 'Unknown' else GroupName end as GroupName,
    PaperId,
    Color,
    Duplex, 
    sum(Page_Printed) As A3PagesPrinted, 
    sum(Cost) as A3TotalCost,
    NULL AS A3DuplexPagesPrinted,
    NULL AS A3DuplexTotalCost
from printstat where paperid = 8 and color = 0 and duplex = 0 
and Date_Print >= '2013-01-01' and Date_Print < '2013-10-21'
group by GroupName, PaperId, Color, Duplex

union all

select case when len(GroupName) = 0 then 'Unknown' else GroupName end as GroupName,
    PaperId,
    Color,
    Duplex,
    NULL,
    NULL,
    sum(Page_Printed) As A3DuplexPagesPrinted, 
    sum(Cost) as A3DuplexTotalCost
from printstat where paperid = 8 and color = 0 
and duplex = 1 and Date_Print >= '2013-01-01' and Date_Print < '2013-10-21'
group by GroupName, PaperId, Color, Duplex

Upvotes: 1

Related Questions