Group by error

Question

Post reply

Group by error

jrw39

Mr or Mrs. 500

Points: 565
More actions
December 18, 2009 at 8:05 am

#215041

I'm getting the following error message:
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'GROUP'.
I don't think the problem is in the group by statement I think the joins are incorrectly written. This is a query i'm updating from access 07 to run in sql management studio 05
-sql-
SELECT [tblGL code percent cost allocation].[Load ID],
Min([tblGL code percent cost allocation].[GL Code]) AS [GL 3],
Min([tblGL code percent cost allocation].[sumofGL Perc of Cost]) AS [GL 3 perc of cost],
[tblGL Codes (Determine GL 1)].[Load ID] AS [Load GL1],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost],
[tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID] AS [Load GL2],
[tblGL Codes (Determine GL 2)].[GL 2],
[tblGL Codes (Determine GL 2)].[GL 2 perc of cost]
INTO [tblGL Codes (Determine GL3)]
FROM [tblGL code percent cost allocation]
LEFT JOIN [tblGL Codes (Determine GL 1)]
ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 1)].[Load ID])
AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 1)].[GL 1])
LEFT JOIN [tblGL Codes (Determine GL 2)]
ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID])
AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 2)].[GL 2]
GROUP BY [tblGL code percent cost allocation].[Load ID],
[tblGL Codes (Determine GL 1)].[Load ID],
[tblGL Codes (Determine GL 1)].[GL 1],
[tblGL Codes (Determine GL 1)].[GL 1 perc of cost],
[tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID],
[tblGL Codes (Determine GL 2)].[GL 2],
[tblGL Codes (Determine GL 2)].[GL 2 perc of cost]
HAVING (([tblGL Codes (Determine GL 1)].[Load ID]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1]) IS NULL)
AND (([tblGL Codes (Determine GL 1)].[GL 1 perc of cost]) IS NULL)
AND (([tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID]) IS NULL)
AND (([tblGL Codes (Determine GL 2)].[GL 2]) IS NULL)
AND (([tblGL Codes (Determine GL 2)].[GL 2 perc of cost]) IS NULL));

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

closing bracket before the GROUP BY is missing.

AND ( [tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 2)].[GL 2] )

Note: This would be much easier to find if the table names would not tell a complete novel....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

SELECT [tblGL code percent cost allocation].[Load ID],

Min([tblGL code percent cost allocation].[GL Code]) AS [GL 3],

Min([tblGL code percent cost allocation].[sumofGL Perc of Cost]) AS [GL 3 perc of cost],

[tblGL Codes (Determine GL 1)].[Load ID] AS [Load GL1],

[tblGL Codes (Determine GL 1)].[GL 1],

[tblGL Codes (Determine GL 1)].[GL 1 perc of cost],

[tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID] AS [Load GL2],

[tblGL Codes (Determine GL 2)].[GL 2],

[tblGL Codes (Determine GL 2)].[GL 2 perc of cost]

INTO [tblGL Codes (Determine GL3)]

FROM [tblGL code percent cost allocation]

LEFT JOIN [tblGL Codes (Determine GL 1)]

ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 1)].[Load ID])

AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 1)].[GL 1])

LEFT JOIN [tblGL Codes (Determine GL 2)]

ON ([tblGL code percent cost allocation].[Load ID] = [tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID])

AND ([tblGL code percent cost allocation].[GL Code] = [tblGL Codes (Determine GL 2)].[GL 2]

GROUP BY [tblGL code percent cost allocation].[Load ID],

[tblGL Codes (Determine GL 1)].[Load ID],

[tblGL Codes (Determine GL 1)].[GL 1],

[tblGL Codes (Determine GL 1)].[GL 1 perc of cost],

[tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID],

[tblGL Codes (Determine GL 2)].[GL 2],

[tblGL Codes (Determine GL 2)].[GL 2 perc of cost]

HAVING (([tblGL Codes (Determine GL 1)].[Load ID]) IS NULL)

AND (([tblGL Codes (Determine GL 1)].[GL 1]) IS NULL)

AND (([tblGL Codes (Determine GL 1)].[GL 1 perc of cost]) IS NULL)

AND (([tblGL Codes (Determine GL 2)].[tblGL code percent cost allocation_Load ID]) IS NULL)

AND (([tblGL Codes (Determine GL 2)].[GL 2]) IS NULL)

AND (([tblGL Codes (Determine GL 2)].[GL 2 perc of cost]) IS NULL));

Also note that you can get more answers, faster and make our lives easier by using the tags, as I have done for your code above. Doesn't that look better?

Just click the "QUOTE" button on this post if you need to see how this is done.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]