Aggregate into custom groups

I'm not sure if this can be done in one stored procedure. I got it working using nested views but I want it to be as concise as possible.

Every time I try to select from a subquery, I get errors that I can't debug. I understand the concepts of how to get an output but struggle with the syntax.

Suppose we have a large table listing every household in the United States in the following format:

[dbo].[US_HOUSEHOLDS_TABLE]

Desired output:

You can do something like this perhaps:

SELECT  *
INTO #data
FROM    (
    VALUES  (1, N'Anderson', N'Robert')
    ,   (1, N'Anderson', N'Mary')
    ,   (1, N'Anderson', N'Cody')
    ,   (1, N'Anderson', N'Brittany')
    ,   (2, N'Carver', N'William')
    ,   (2, N'Carver', N'Suzanne')
    ,   (3, N'Washington', N'Fred')
    ,   (3, N'Washington', N'Ethel')
    ,   (3, N'Washington', N'Tony')
    ,   (4, N'Smith', N'Darlene')
    ,   (4, N'Smith', N'Amy')
    ,   (4, N'Smith', N'Richard')
    ,   (4, N'Smith', N'Darlene')
    ,   (4, N'Smith', N'Amy')
    ,   (4, N'Smith', N'Richard')
    ,   (5, 'Sigge', 'Mannen')
    ,   (5, 'Stack', 'Overflow')
    
) t (HOUSEHOLD_ID,FAMILY_NAME,MEMBER_NAME)

SELECT  CASE WHEN cnt < 6 THEN CAST(cnt AS VARCHAR(30)) ELSE '6 or more' END AS HouseHold
,   COUNT(*) AS Total
FROM    (
    SELECT  count(*) AS cnt
    FROM    #data
    GROUP BY HOUSEHOLD_ID
    ) x
GROUP BY CASE WHEN cnt < 6 THEN CAST(cnt AS VARCHAR(30)) ELSE '6 or more' END

First, you get the count by the household and then you group by that count.

Those with 6+ get their own group.

Outputs: