How do I group in a union?

[Sam]

Hi,

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.
--
Thanks,

Sam

[--CELKO--]

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.

[Kalen Delaney]

Hi Sam

First of all, it's rarely useful to just say that something doesn't work.
Does that mean you got an error message? If so, tell us what the message
said. Does it mean SQL Server crashed? Does it mean you didn't get expected
results? If so, show us some sample data, what you expect, and what you got.
"Doesn't work" can mean all kinds of different things.

That being said, I will just make some general suggestions.

You should read about GROUP BY in the Books Online. The GROUP BY applies
only to a single select, not to the result of the UNION. It is used to take
multiple rows with matching values and return one row of output. So this:

SELECT EmployeeID, ProjectID
FROM DB2.table1
GROUP BY EmployeeID

is telling SQL Server to return one output row for each unique EmployeeID.
But since ProjectID values will be different, SQL Server doesn't know what
to do with the different ProjectIDs and it gives an error.

You either have to say you only want the largest or smallest or whatever
ProjectID, or you have to leave it out.

SELECT EmployeeID, max(ProjectID)

FROM DB2.table1
GROUP BY EmployeeID

If what you really want to do is SORT by EmployeeID, you can use ORDER BY at
the end of the query and that will apply to everything that is UNION'd
together.

If you really want to do GROUP BY on the results of the UNION, you can turn
the UNION into a derived table and do something like this:

SELECT EmployeeID, max(ProjectID)
FROM (SELECT EmployeeID, ProjectID

FROM DB1.table1
UNION
SELECT EmployeeID, ProjectID

FROM DB2.table1) as t1
GROUP BY EmployeeID

Also, UNION always tries to remove duplicates. If you are not expecting any
duplicates you might consider using UNION ALL instead.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

"Sam" <S...@discussions.microsoft.com> wrote in message
news:06CFE371-B78B-4E23...@microsoft.com...

[Sam]

Thank you both for your help.
--
Thanks,

Sam