How do I group in a union?

SQL Apprentice Question
I have a select statement that uses union to pull data from multiple
databases and return them in a single recordset. I want to group these
results using group by. How do I do that?


Here's what I have:


SELECT EmployeeID, ProjectID
FROM DB1.table1
UNION
SELECT EmployeeID, ProjectID
FROM DB2.table1


This works fine but I want to group all projects by EmployeeID. I tried the
following but it didn't work


SELECT EmployeeID, ProjectID
FROM DB1.table1
UNION
SELECT EmployeeID, ProjectID
FROM DB2.table1
GROUP BY EmployeeID


I'd appreciate some help here.


Celko Answers
The results of a UNION do not have column names

SELECT X.emp_id, COUNT(project_id) AS project_tot
FROM (SELECT emp_id, project_id
FROM DB1.Table1
UNION
SELECT emp_id, project_id
FROM DB2.Table1)
AS X(emp_id, project_id)
GROUP BY X.emp_id;


UNION ALL will be faster, if it is possible.




Original source

giving one union preference

SQL Apprentice Question
Let say I have a query with the following structure:

Select name, mdate, kdate
from table1 a
inner join (
select name, mdate
from table2 b
where a.id = b.id
and mdate >= kdate
union
select name, mdate
from table3 c
where a.id = c.id
and mdate >= kdate
) mindate


When the select mdate from the inner join union (mindate) returns the
same mdate for table b and c, I want to give preference to the c.mdate
and so c.name.


Is there a way to do this?




Celko Answers
>> When the select mdate from the inner join union (mindate) returns the same mdate for table b and c, I want to give preference to the c.mdate and so c.name. <<


This question makes no sense. The poorly named derived table,
Mindate, acts as if it is materialized and you no longer have access
to tables B and C. It is like asking to see the eggs after the cake
has been baked.

Original Source