Group By problem (1 Viewer)

[wgma]

I need to run the query below:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS Year, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, Year

But I am having a problem with the term Year in the group by clause. How do I group by just the year portion of the date field which is yyyymmdd?

Thanks.

 

[pbaldy]

Try grouping by the expression rather than the alias:

MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

 

[boblarson]

1. do not use YEAR as your column name as it is an Access Reserved Word.

2. You would use:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS MyYear, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

 

[boblarson]

Try grouping by the expression rather than the alias:

MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

too quick for me.

 

[wgma]

1. do not use YEAR as your column name as it is an Access Reserved Word.

2. You would use:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS MyYear, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

I have tried putting the expression into the group by but I get an ODBC error.

ODBC--call failed.
Argument 1 of function SUBSTRING not valid. (#-171)

Any suggestions?

 

[boblarson]

Does the query run without the group by?

 

[wgma]

Does the query run without the group by?

Yes, it runs.

 

[boblarson]

Try using the query without the grouping and then add that into a new query and do the grouping there.