Post reply

Sort/Sum Records -- Please Help

  • November 17, 2004 at 11:15 pm

    #60259

    Please post some sample code on how to "GROUP BY"

    1. if customer name is "LIKE" digits before the "-" sign AND

    2. state/city are the same THEN

    3. extract customer, city, state

    Used to answer the "you have 3 BOB's stores in CUPERTINO, CA" question in one "BIG" report, displaying other stores below it.

    CustomerCitySt
    JOHN FOODS-JUICE BAR         CAMPBELL                      CA
    JOHN FOODS-CAMPBELL          CAMPBELL                      CA
       
    BOB'S - GROCERY           CUPERTINO                     CA
    BOB'S - HOUSEWARES        CUPERTINO                     CA
    BOB'S - CUPERTINO-NUTRITIOCUPERTINO                     CA
       
    JOHN FOODS - COFFEE BAR        FRESNO                        CA
    JOHN FOODS - FRESNO            FRESNO                        CA
       
    JOHN FOODS - COFFEE BAR        GLENDALE                      CA
    JOHN FOODS - GROCERY         GLENDALE                      CA
       

    Thanks for the samples . . .

  • November 18, 2004 at 12:12 am

    #530582

    no guarentees, but it should give you some idea

    select count(customer), left(customer,CHARINDEX ('-', customer)-1) AS StoreName, city, st

    from table

    group by left(customer,CHARINDEX ('-', customer)-1), city, st

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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