Group By

I am try to write a query that would group my data
this is what I have
SELECT [CORP-NUM],
[GL-ACCT-NUM-II-PRIN],
[GL-ACCT-NUM-II-IE],
[CURRENT-BOOK-PRINCIPAL],
sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
(dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
dbo.current_month_09_2005.BookAmount)as Diff,
SUBSYSID
FROM dbo.Copy_AFS
LEFT JOIN dbo.current_month_09_2005
ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
GROUP BY[CORP-NUM]
Howver I am getting the following error
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-IE' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.current_month_09_2005.BookAmount' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.SUBSYSID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Can some one tell me how to fix this
Thanks
ChrisAll non-aggregate columns in your SELECT list must appear in the GROUP BY.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A980D39B-E799-4267-9318-CE76A967DD7C@.microsoft.com...
I am try to write a query that would group my data
this is what I have
SELECT [CORP-NUM],
[GL-ACCT-NUM-II-PRIN],
[GL-ACCT-NUM-II-IE],
[CURRENT-BOOK-PRINCIPAL],
sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
(dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
dbo.current_month_09_2005.BookAmount)as Diff,
SUBSYSID
FROM dbo.Copy_AFS
LEFT JOIN dbo.current_month_09_2005
ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
GROUP BY[CORP-NUM]
Howver I am getting the following error
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-IE' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.CURRENT-BOOK-PRINCIPAL' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.current_month_09_2005.BookAmount' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 5
Column 'dbo.Copy_AFS.SUBSYSID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
Can some one tell me how to fix this
Thanks
Chris|||Each column listed in the SELECT clause must be listed in the GROUP BY claus
e
(except - of course - the aggregate):
select <column list>
,<aggregate>
from <table>
group by <column list>
What exactly is your goal?
ML
http://milambda.blogspot.com/|||Chris (Chris@.discussions.microsoft.com) writes:
> I am try to write a query that would group my data
> this is what I have
> SELECT [CORP-NUM],
> [GL-ACCT-NUM-II-PRIN],
> [GL-ACCT-NUM-II-IE],
> [CURRENT-BOOK-PRINCIPAL],
> sum (dbo.current_month_09_2005.BookAmount)as [Book Amount],
> (dbo.Copy_AFS.[CURRENT-BOOK-PRINCIPAL]-
> dbo.current_month_09_2005.BookAmount)as Diff,
> SUBSYSID
> FROM dbo.Copy_AFS
> LEFT JOIN dbo.current_month_09_2005
> ON dbo.Copy_AFS.Key_tdm = dbo.current_month_09_2005.Key_Tdm
> GROUP BY[CORP-NUM]
> Howver I am getting the following error
> Server: Msg 8120, Level 16, State 1, Line 5
> Column 'dbo.Copy_AFS.GL-ACCT-NUM-II-PRIN' is invalid in the select list
> because it is not contained in either an aggregate function or the GROUP
> BY clause.
>...
> Can some one tell me how to fix this
No, becase I don't know what outupt you are really looking for. The error
would away ir you add all non-aggregated columns (that is, those outside
the SUM()) to the GROUP BY clause. But whether that will give you the
result you are looking for, I have no idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx