Please help with this Group By Clause

Question

Post reply

Please help with this Group By Clause

paula_sql

SSCrazy

Points: 2665
More actions
July 29, 2009 at 9:56 pm

#156038

Can someone please tell me why this code doesn't work. Thanks in advance. The error message is below:
Msg 156, Level 15, State 1, Line 61
Incorrect syntax near the keyword 'AS'.
SELECT a.WS_Job_Number,
a.Year1,
a.PERIODID,
CASE
WHEN a.PeriodID = 1 THEN 'July'
WHEN a.PeriodID = 2 THEN 'August'
WHEN a.PeriodID = 3 THEN 'September'
WHEN a.PeriodID = 4 THEN 'October'
WHEN a.PeriodID = 5 THEN 'November'
WHEN a.PeriodID = 6 THEN 'December'
WHEN a.PeriodID = 7 THEN 'January'
WHEN a.PeriodID = 8 THEN 'February'
WHEN a.PeriodID = 9 THEN 'March'
WHEN a.PeriodID = 10 THEN 'April'
WHEN a.PeriodID = 11 THEN 'May'
WHEN a.PeriodID = 12 THEN 'June'
END AS 'Month',
a.Cost_Element,
b.Cost_Element_Desc,
rtrim(a.Cost_Code_Number_1) + rtrim(a.Cost_Code_Number_2) +
rtrim(a.Cost_Code_Number_3) + rtrim(a.Cost_Code_Number_4) as Cost_Code,
c.Cost_Code_Description,
sum(a.Cost_Code_Actual_Cost),
sum(a.Actual_Units_TTD),
sum(a.Revsd_Forecast_Cost),
sum(a.Revsd_Forecast_Units)
FROM JC20002 a
inner join JC40209 b on a.Cost_Element = b.Cost_Element
left join JC40202 c on a.Cost_Code_Number_1 = c.Cost_Code_Number_1
and a.Cost_Code_Number_2 = c.Cost_Code_Number_2
and a.Cost_Code_Number_3 = c.Cost_Code_Number_3
and a.Cost_Code_Number_4 = c.Cost_Code_Number_4
and a.Cost_Element = c.Cost_Element
--where a.WS_Job_Number = 'PAULBUDGET'
--order by 1,2,3,5
Group By
a.WS_Job_Number,
a.Year1,
a.PERIODID,
CASE
WHEN a.PeriodID = 1 THEN 'July'
WHEN a.PeriodID = 2 THEN 'August'
WHEN a.PeriodID = 3 THEN 'September'
WHEN a.PeriodID = 4 THEN 'October'
WHEN a.PeriodID = 5 THEN 'November'
WHEN a.PeriodID = 6 THEN 'December'
WHEN a.PeriodID = 7 THEN 'January'
WHEN a.PeriodID = 8 THEN 'February'
WHEN a.PeriodID = 9 THEN 'March'
WHEN a.PeriodID = 10 THEN 'April'
WHEN a.PeriodID = 11 THEN 'May'
WHEN a.PeriodID = 12 THEN 'June'
END AS 'Month',
a.Cost_Element,
b.Cost_Element_Desc,
rtrim(a.Cost_Code_Number_1) + rtrim(a.Cost_Code_Number_2) +
rtrim(a.Cost_Code_Number_3) + rtrim(a.Cost_Code_Number_4) as Cost_Code,
c.Cost_Code_Description

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The error is because you have included aliases for the field in the GROUP BY clause. Try the following instead

SELECT a.WS_Job_Number,

a.Year1,

a.PERIODID,

CASE

WHEN a.PeriodID = 1 THEN 'July'

WHEN a.PeriodID = 2 THEN 'August'

WHEN a.PeriodID = 3 THEN 'September'

WHEN a.PeriodID = 4 THEN 'October'

WHEN a.PeriodID = 5 THEN 'November'

WHEN a.PeriodID = 6 THEN 'December'

WHEN a.PeriodID = 7 THEN 'January'

WHEN a.PeriodID = 8 THEN 'February'

WHEN a.PeriodID = 9 THEN 'March'

WHEN a.PeriodID = 10 THEN 'April'

WHEN a.PeriodID = 11 THEN 'May'

WHEN a.PeriodID = 12 THEN 'June'

END AS 'Month',

a.Cost_Element,

b.Cost_Element_Desc,

rtrim(a.Cost_Code_Number_1) + rtrim(a.Cost_Code_Number_2) +

rtrim(a.Cost_Code_Number_3) + rtrim(a.Cost_Code_Number_4) as Cost_Code,

c.Cost_Code_Description,

sum(a.Cost_Code_Actual_Cost),

sum(a.Actual_Units_TTD),

sum(a.Revsd_Forecast_Cost),

sum(a.Revsd_Forecast_Units)

FROM JC20002 a

inner join JC40209 b on a.Cost_Element = b.Cost_Element

left join JC40202 c on a.Cost_Code_Number_1 = c.Cost_Code_Number_1

and a.Cost_Code_Number_2 = c.Cost_Code_Number_2

and a.Cost_Code_Number_3 = c.Cost_Code_Number_3

and a.Cost_Code_Number_4 = c.Cost_Code_Number_4

and a.Cost_Element = c.Cost_Element

--where a.WS_Job_Number = 'PAULBUDGET'

--order by 1,2,3,5

Group By

a.WS_Job_Number,