Post reply

Select Distinct

  • May 4, 2005 at 4:08 pm

    #163390

    Hi, I need to count distinct records in a table. Actual data looks like this.

    D0004A

    D0004B

    D0005C

    D0008B

    D0005A

    I need to count how many D0004 exist in the table, It doesn't matter if it has A,B,C or any onther character.

    Thanks for your help

     

     

  • May 4, 2005 at 4:12 pm

    #556942

    SELECT LEFT(YourColumn,5) AS Code, Count(*) AS Records

    FROM dbo.YourTable

    GROUP BY LEFT(YourColumn,5)

    LinkedIn Profile

  • May 5, 2005 at 9:36 am

    #557115

    If you wanted to completely eliminate alpha characters, including the leading one, you could use the SUBSTRING function:

    SELECT substring(account_k, 2, 4) AS Code, Count(*) AS Records

    FROM dbo.YourTable

    [WHERE substring(account_k, 2, 4) = '0004']

    GROUP BY SUBSTRING(YourColumn,2,4)

    HTH

    Don

  • May 5, 2005 at 10:58 am

    #557165

    ??

    Select Count(*) from Yourtable where YourColumn like 'D0004%'


    * Noel

  • May 5, 2005 at 11:02 am

    #557167

    the problem is when the character is not allways in the same position, it could be D00A, OR D000000A, the data stored at the column has different lenght.

     

    Thank for the help and comments.

  • May 5, 2005 at 11:09 am

    #557171

    I don't really understand  how you want to group them but:

    Ist this what you are after ?

    SELECT LEFT(YourColumn,Len(YourColumn)-1) AS Code, Count(*) AS Records

    FROM dbo.YourTable

    GROUP BY LEFT(YourColumn,Len(YourColumn)-1)

    [Edit:] Credit To David Post

     


    * Noel

  • May 6, 2005 at 12:03 pm

    #557436

    (weird, the system just deleted PART of my post!)

    If your data is not consistent, it's either going to have to be cleaned up so that it is or you're going to have to live with problems.  Data being different lengths is not always a problem as long as the left-aligned significant parts are consistent.

    Assuming the data element's definition is something like this: "Element starts with a D, contains one or more zeroes, contains a single numeric digit, followed by one letter" then it can be cleaned up through string manipulation possibly with pattern matching.

    But not knowing what rules build that value, it's hard to offer a good solution.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

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

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