Union query

[kimsMTC]

Can someone please tell me why I get an error message on this query that JobType is not included in the first query?

SELECT Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, 'Regular' AS JobType, Sum(Time.Hours) AS SumOfHours
FROM Department INNER JOIN (Employee INNER JOIN [Time] ON Employee.EmployeeID = Time.EmployeeID) ON Department.DepartmentID = Employee.DepartmentID
GROUP BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, JobType
ORDER BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, JobType
UNION
SELECT Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, NonProjectType.NonProjectTypeName As JobType, Sum(NonProjectTime.Hours) AS SumOfHours
FROM Department INNER JOIN ((Employee INNER JOIN NonProjectTime ON Employee.EmployeeID = NonProjectTime.EmployeeID) INNER JOIN NonProjectType ON NonProjectTime.NonProjectTypeID = NonProjectType.NonProjectTypeID) ON Department.DepartmentID = Employee.DepartmentID
GROUP BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, JobType
ORDER BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, JobType;

Thanks!

 

[PHV]

You can't use an alias in the GROUP BY clause.
However you haven't to put a constant value in this clause.
Furthermore, column alias are only meaningful in the first SELECT statement of an UNION query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rac2]

No but the error message may be misleading. (When did that ever happen.)

I believe the syntax for a UNION query allows one ORDER BY clause and it must be the last clause. It applies to the whole result set, not to the individual queries. So that would be something to try.

SELECT stuff
FROM aTable

UNION

SELECT similarstuff
FROM bTable

ORDER BY stuff

 

[kimsMTC]

OK, I took both the suggestions given to me and now I get a new error message:

SELECT Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, 'Regular' AS NonProjectTypeName, Sum(Time.Hours) AS SumOfHours
FROM Department INNER JOIN (Employee INNER JOIN [Time] ON Employee.EmployeeID = Time.EmployeeID) ON Department.DepartmentID = Employee.DepartmentID

UNION
SELECT Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, NonProjectType.NonProjectTypeName, Sum(NonProjectTime.Hours) AS SumOfHours
FROM Department INNER JOIN ((Employee INNER JOIN NonProjectTime ON Employee.EmployeeID = NonProjectTime.EmployeeID) INNER JOIN NonProjectType ON NonProjectTime.NonProjectTypeID = NonProjectType.NonProjectTypeID) ON Department.DepartmentID = Employee.DepartmentID
GROUP BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, NonProjectTypeName
ORDER BY Department.DepartmentName, Employee.EmployeeNumber, Employee.LastName, Employee.FirstName, NonProjectTypeName;

"You tried to execute a query that does not include DepartmentName as part of the aggregate."

I don't understand this because I grouped by this.

Thanks!

 

[PHV]

what about this ?
SELECT D.DepartmentName, E.EmployeeNumber, E.LastName, E.FirstName, 'Regular' AS JobType, Sum(T.Hours) AS SumOfHours
FROM Department AS D INNER JOIN (Employee AS E INNER JOIN [Time] AS T ON E.EmployeeID = T.EmployeeID) ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentName, E.EmployeeNumber, E.LastName, E.FirstName
UNION ALL
SELECT D.DepartmentName, E.EmployeeNumber, E.LastName, E.FirstName, N.NonProjectTypeName, Sum(T.Hours)
FROM Department AS D INNER JOIN ((Employee AS E INNER JOIN NonProjectTime AS T ON E.EmployeeID = T.EmployeeID) INNER JOIN NonProjectType AS N ON T.NonProjectTypeID = N.NonProjectTypeID) ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentName, E.EmployeeNumber, E.LastName, E.FirstName, N.NonProjectTypeName
ORDER BY 1, 2, 3, 4, 5;


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rac2]

Probably you thought the GROUP BY clause could be applied to the results of the UNION just as the ORDER BY. There is no rhyme or reason to it but it is as PHV suggests.

His query will give separate totals for the staff in the two queries.

If you actually wish to get aggregate the results of the UNION, one SUM for one employee, then you might try first creating the UNION query with the detail rows and saving it. Then do the GROUP BY query on that.