Alternative to UNION 1

[NiceArms]

Hi everyone,

an issue with union:

if i put two select statements together I do get both the answers I require, however when I execute the code I get a single row for each select statement (I suppose this sould be expected). As there is a good chance I have not explained myself very well, follows is a simple example:

SELECT ColumnA, Count(ColumnB) AS 'Test1', '' AS 'Test2'
FROM DB1
WHERE ColumnB = 7
UNION
SELECT ColumnA, '', Count(ColumnC)
FROM DB1
WHERE ColumnC >1
GROUP BY ColumnA

please note I have written this example without testing, however I do not have an issue with the above the issue comes when the data is displayed:

ColumnA Test1 Test2
A1 2 0
A1 0 1
A2 5 0
A2 0 8

As you can see I get the result grouped but over 2 rows where I require them in a single row e.g.:

ColumnA Test1 Test2
A1 2 1
A2 5 8

Any useful hints would be great.

Thank you

/Nice

 

[fredericofonseca]

untested - but try the following.

select ColumnA, sum(test1), sum(test2)
from (
SELECT ColumnA, Count(ColumnB) AS 'Test1', '' AS 'Test2'
FROM DB1
WHERE ColumnB = 7
UNION
SELECT ColumnA, '', Count(ColumnC)
FROM DB1
WHERE ColumnC >1
) tmp
GROUP BY ColumnA

Note that on your original code the GROUP by applies to the second select - not to the union - hence you not getting what you want


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[markros]

It sounds to me you want a self-join instead, e.g.

select COALESCE(A.ColumnA, B.ColumnA), A.Test1, B.Test2 from
(select
ColumnA, Count(ColumnB) AS 'Test1'
FROM DB1
WHERE ColumnB = 7) A FULL JOIN (your other select) B on A.Column1 = B.ColumnA

 

[gmmastros]

You're right. UNION isn't really what you want here. Instead, you probably want to use derived tables, like this.

Select Coalesce(A.ColumnA, B.ColumnA) As ColumnA, A.Test2, B.Test2
From   (
       SELECT ColumnA, 
              Count(ColumnB) AS 'Test1'
       FROM   DB1
       WHERE  ColumnB = 7
       ) As A
       Full Outer Join (
         SELECT ColumnA, Count(ColumnC) As Test2
         FROM   DB1
         WHERE  ColumnC >1
         GROUP BY ColumnA
         ) As B
         On A.ColumnA = B.ColumnA

Now, notice a couple interesting things. I used a full outer join because the ColumnA data could exist in either table, or both tables. Since we are joining, we need to make sure the join returns data whether the row is missing from either derived table. If we left, right, or inner join the derived tables, you could potentially miss data that you want to return.

Also, because of this issue, I use Coalesce on the ColumnA value, just to make sure we always have something to return.

Lastly..... and this is purely based on the two queries...

We can sometimes simplify things a bit. In your case, it seems as though you want a count of rows where ColumnB = 7 and another count of rows where ColumnC > 1. The count function will ignore NULLS, so by using some crafty Case/When's we can get both counts with a single pass through the table. This query will run faster (while still providing the same results).

SELECT ColumnA, 
       Count(Case When ColumnB = 7 Then 1 End) As Test1,
       Count(Case When ColumnC > 1 Then 1 End) As Test2
FROM   DB1
GROUP BY ColumnA

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Ah... The missing Group By in the first statement.

Select Coalesce(A.ColumnA, B.ColumnA) As ColumnA, A.Test2, B.Test2
From   (
       SELECT ColumnA, 
              Count(ColumnB) AS 'Test1'
       FROM   DB1
       WHERE  ColumnB = 7
       GROUP BY ColumnA
       ) As A
       Full Outer Join (
         SELECT ColumnA, Count(ColumnC) As Test2
         FROM   DB1
         WHERE  ColumnC >1
         GROUP BY ColumnA
         ) As B
         On A.ColumnA = B.ColumnA

Still.... the case/when query will return the same data and will execute faster.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NiceArms]

Thank you for your responses.

It is 21:36 where I am but I will post back tomorrow once I have tested both

/nice

 

[markros]

I guess I've been here long enough (couple of months) to start thinking exactly like George (but never would be able to explain the things as good) )

 

[NiceArms]

Great, the CASE COUNT worked a treat thank you

/nice