how to count a data.

  • hi,

    my data is like this.

    ---------code----------------name

    ACT706022774CCI0---------CCI

    ACT706022774CCI0---------CCI

    CDF892782322CCI4---------CCI

    CDF892782322CCI4---------CCI

    CDF892782322CCI4---------CCI

    i want a query that show data like this.

    ---------code--------------------name

    ---------ACT ----------------------2

    ---------CDF-----------------------3

    thanks for the help.

    immad

  • So create a column in your base query that grabs the first three characters of the string, and then base your summary query on it.

  • Dear Immaduddin,

    Use the Below mentioned query

    Select Distinct(LEFT(Code,3)) as CODE,COUNT(Name) as Name

    From [TABLENAME]

    Group by LEFT(Code,3)

    Hope it Helps..!!

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

  • Shafat Husain (12/29/2014)


    Dear Immaduddin,

    Use the Below mentioned query

    Select Distinct(LEFT(Code,3)) as CODE,COUNT(Name) as Name

    From [TABLENAME]

    Group by LEFT(Code,3)

    Why the distinct when you have a group by, and why are you wrapping superfluous brackets around the LEFT? It makes it look like DISTINCT is a function, which I'm sure you're aware it's not.

    With the unnecessary distinct and brackets taken out:

    Select LEFT(Code,3) as CODE, COUNT(*) as Name

    From [TABLENAME]

    Group by LEFT(Code,3)

    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
  • Dear Gila,

    I started the query with

    Select Distinct(Left(Code,3)..... to check for the first three characters.

    and then added the rest of the code.

    Forgot to remove the Distinct. 😛

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

  • I still don't understand why you're putting brackets around DISTINCT as if it was a function. All that's going to do is confuse people who read the code.

    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
  • Old bad habits die hard...!! :blush:

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

Viewing 7 posts - 1 through 6 (of 6 total)

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