Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    7

    Question Unanswered: 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.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2004
    Posts
    7
    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?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Can you post the SQL statement?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Dec 2004
    Posts
    7
    Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I don't see why this wouldn't work

    Code:
    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
    Inspiration Through Fermentation

  7. #7
    Join Date
    Dec 2004
    Posts
    7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •