Column is invalid + Aggregate function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Column is invalid + Aggregate function

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-11 : 10:51:32

I have the following query

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT
union
select * from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')

GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
order by employeenumber asc

and when I execute it, I receive the error:

"Column 'scratchpad5.EmployeeNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
."

Can someone please assist.

Thank you

Doug

if I remove the union out of this query, it works.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 10:58:37

SELECT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT
GROUP BY
[ScratchPad5].EmployeeNumber,[sumhours]

union
select <corresponding columns to above>
from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')

order by employeenumber asc

The stuff that goes on before the UNION is totally separate from the stuff that comes after the union.

Jim

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-11 : 12:21:51

Jim,

Ok so I changed the query to reflect the edits you suggested. The only corresponding column between the two tables is the Employeenumber, so I have this query:


SELECT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40 
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours, 
SUM( case when [sumhours]>40 
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0 
END ) AS TotalOT 
GROUP BY 
[ScratchPad5].EmployeeNumber, 
sumhours

union 
select employeenumber 
from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')

order by employeenumber asc

and I get the error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'ScratchPad5' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'ScratchPad5' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'ScratchPad5' does not match with a table name or alias name used in the query.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 12:46:14

You need to select from, I missed that your original query didn't have that.

SELECT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT
FROM ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours

your next error will involve the numebr of columns not matching between your union statements

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-11 : 12:57:44

I've since corrected that but now I'm getting this error:

All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 13:18:15

Hence this prediction

quote:

your next error will involve the number of columns not matching between your union statements

Why do you think that you got this error?

Jim

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-11 : 13:38:41

Jim,

So if there's only one column between the two tables that match, how do I go about getting the results that I need?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 13:43:46

union
select employeenumber ,<0 or null or -1,etc.>
from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')

You now have as many columns below as you do above.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources