Grouped by Using aliased names

Hi,

I have a select statement that gives me an output as follows:

Date Store Num Location
4-5-2007 0001 NY
4-5-2007 0002 NY
4-5-2007 0002 NY
4-4-2007 0003 PA
4-4-2007 0002 PA

The store num and location columns are derived like so:
LEFT(Table.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(Table.WholeField, 6), 2) AS 'Location'

The problem I am running into is that I have been tasked to write a select statement that sums up distinct values for Store Num and Location. The output should look something like this :

Date Store Num Location Num
4-5-2007 0002 NY 2

However, 'Store Num' and 'Location' comes from one field by design. I have written a select statement that uses the GROUP BY function to get the correct output. However, I am receiving an invalid column name error because I am using an aliased name.

Does anyone have any insight into the error or a possible workaround.

Thanks,
V.Don't use the alias in the Group By, use the expression instead.

GROUP BY LEFT(Table.WholeField, 4), RIGHT(LEFT(Table.WholeField, 6), 2)

or just bury the original query as a subquery, and then sum and group by.

The first version seems "cleaner" to me though|||Thanks for the quick response. The Grouping by for the expressions works.

However, I am still getting:

Date Store Num Location Num
4-5-2007 0002 NY 1
4-5-2007 0002 NY 1

However, I am looking for :

Date Store Num Location Num
4-5-2007 0002 NY 2

Shouldn't the group by statement work with a correct count(*) or do I have to issue counts for the two separate columns?|||Can you post the SQL statement?|||Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!|||I don't see why this wouldn't work

SELECT [DATE],
LEFT(#TMP.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(#TMP.WholeField, 6), 2) AS 'Location',
COUNT(*)
FROM #TMP
GROUP BY [DATE],
LEFT(#TMP.WholeField, 4),
RIGHT(LEFT(#TMP.WholeField, 6), 2)

That yields

Date Store Num Location Num
4-4-2007 0002 PA 1
4-4-2007 0003 PA 1
4-5-2007 0001 NY 1
4-5-2007 0002 NY 2|||I was using a convert function to take the timestamp field Date and convert it to a MM-DD-YYYY format. However, in the group by statement i was using just the Fieldname Date. When I added the convert function to the group by, it worked.