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

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.