sql errors

Question

sql errors

rui_fro

SSC-Addicted

Points: 467
More actions
April 22, 2008 at 4:58 am

#181592

Hi!
I'm trying to do this:
INSERT INTO FilteredCaseIntermDetail(b.casenumber,b.owner,b.dataactivity,b.sumtime,
b.project,b.billingout,b.encomenda)
(SELECT a.casenumber,a.owner,a.dataactivity,
sum(a.sumtime),a.project,a.billingout,a.encomenda
FROM FilteredCaseInterm as a, FilteredCaseIntermDetail as b
WHERE a.statusactivity = 'Completed'
GROUP BY a.dataactivity)
but I get the error:
Msg 8120, Level 16, State 1, Line 1
Column 'FilteredCaseInterm.casenumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can please someone help me?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

All of the fields referenced in the select that are not already part of an aggregrate function need to be in the group by clause (or include them in an aggregrate function).

ie. GROUP BY a.casenumber, a.owner, a.dataactivity, a.project, a.billingout, a.encomenda

BTW, did you notice that you are joining the FilteredCaseInterm table with the FilteredCaseIntermDetail table without any joining condition? You're going to get every "Completed" FilteredCaseInterm record joined with every record in the FilteredCaseIntermDetail table.

rui_fro (4/22/2008)
Hi!
I'm trying to do this:
INSERT INTO FilteredCaseIntermDetail(b.casenumber,b.owner,b.dataactivity,b.sumtime,
b.project,b.billingout,b.encomenda)
(SELECT a.casenumber,a.owner,a.dataactivity,
sum(a.sumtime),a.project,a.billingout,a.encomenda
FROM FilteredCaseInterm as a, FilteredCaseIntermDetail as b
WHERE a.statusactivity = 'Completed'
GROUP BY a.dataactivity)
but I get the error:
Msg 8120, Level 16, State 1, Line 1
Column 'FilteredCaseInterm.casenumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can please someone help me?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

rui_fro SSC-Addicted Points: 467 More actions · Answer 2

thanks!

yes, i noticed that i was not joining them!

[]