none
Need to count characters and group by that result RRS feed

  • Question

  • Noob here. I have a large table and I need to

    • count the number of characters in a single column U
    • where records have a corresponding value in another column Y
    • and group by the character count, so that I can see how many records in column U have 13 characters, 12, 11, etc.

    So far I am at:

    SELECT U, LEN(U) as Digits  
     FROM Table_OfExcitingStuff
    WHERE [Y] IS NOT NULL

    Group By.... Order By...?

    I'm sorry if this has been asked elsewhere but I just can't find anything close to answering. Thanks.

    Friday, September 20, 2019 3:27 PM

All replies

  • Hi Mateo,

    CTE expression is very helpful for your scenario. Please try the following:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, U VARCHAR(100) NOT NULL, Y VARCHAR(20) NULL);
    INSERT INTO @tbl (U, Y)
    VALUES ('12345', 'Miami')
    	, ('123', NULL)
    	, ('123456789', 'Orlando')
    	, ('123456789', 'Fort Lauderdale');
    -- DDL and sample data population, end
    
    ;WITH rs AS
    (
    	SELECT U, LEN(U) as Digits  
    	FROM @tbl
    	WHERE [Y] IS NOT NULL
    )
    SELECT *, COUNT(*) AS [Counter]
    FROM rs
    GROUP BY u, digits
    ORDER BY u, digits DESC;
    

    Output:
    U	Digits	Counter
    12345	5	1
    123456789	9	2


    Friday, September 20, 2019 3:55 PM
  • Hi Mateo,

    You can also use sub-queries.

    If you want to use 'group by', you need to contain an aggregate function (e.g. sum, avg, max, count) before. Here's another method you can refer to:

    create table test ( [U] varchar(20),
    [Y] varchar(20))
    go
    insert into test values
    ('APPLE124456','THE FIRST STRING'),
    ('BANANA14','THE SECOND STRING'),
    ('CCCC6E433DFFFFFF','THE THIRD STRING'),
    ('APPLE124456','THE FORTH STRING')
    
    
    SELECT [U],DIGITS,COUNT(Digits)AS NUMBER FROM (
    SELECT *,len([U])AS Digits   
    FROM TEST
    WHERE [Y] IS NOT NULL)A 
    GROUP BY [Digits],[U]
    ORDER BY Digits
    
    /*
    U                    DIGITS      NUMBER
    -------------------- ----------- -----------
    BANANA14             8           1
    APPLE124456          11          2
    CCCC6E433DFFFFFF     16          1
    */


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, September 23, 2019 1:58 AM
  • Thank you! I will give both of these a try and see how they do, and will mark appropriately.
    Tuesday, September 24, 2019 4:43 PM
  • Thank you much! I will try this.
    Tuesday, September 24, 2019 4:44 PM
  • Hi mateo Luis,

    Try this,

    DECLARE @Sample TABLE (U VARCHAR(100) NOT NULL, Y VARCHAR(20) NULL);
    INSERT INTO @Sample (U, Y)
    VALUES ('12345', 'Miami')
    	, ('123', NULL)
    	, ('123456789', 'Orlando')
    	, ('123456789', 'Fort Lauderdale');
    -- DDL and sample data population, end
    
    SELECT U, LEN(U) as Digits, COUNT(*) AS [Counter]
    FROM @Sample
    WHERE [Y] IS NOT NULL
    GROUP BY u, LEN(U)
    ORDER BY u, digits DESC;
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Tuesday, September 24, 2019 5:52 PM
  • Hi Mateo,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers or share your methods with us. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 8:29 AM