Aggregate into custom groups

Question

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]

HOUSEHOLD_ID	FAMILY_NAME	MEMBER_NAME
1	Anderson	Robert
1	Anderson	Mary
1	Anderson	Cody
1	Anderson	Brittany
2	Carver	William
2	Carver	Suzanne
3	Washington	Fred
3	Washington	Ethel
3	Washington	Tony
4	Smith	Darlene
4	Smith	Amy
4	Smith	Richard

Desired output:

FAMILY_SIZE	FREQUENCY
1	23,903,124
2	57,116,548
3	38,617,295
4	12,397,106
5	3,108,284
6 or more	1,393,571

I'm sorry, the tables looked correct in the real time review but when I published it, the first table didn't format correctly. — WannaKatana
– WannaKatana, Commented Nov 20, 2024 at 18:43
Isn't that just a simple group by + count? Please show us your attempt that has errors. — Dale K
– Dale K, Commented Nov 20, 2024 at 18:44
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Nov 20, 2024 at 19:09

siggemannen · Accepted Answer · 2024-11-20 18:47:05Z

0

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:

HouseHold	Total
2	2
3	1
4	1
6 or more	1

answered Nov 20, 2024 at 18:47

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WannaKatana Over a year ago

Perfect, thank you. I don't know why I have so much trouble with syntax with T-SQL. I know HOW to do what I want but when I try to code it, the debugging errors are so unhelpful and when I look at examples online, it seems it's exactly the same syntax yet it says it's wrong. I never had this much trouble coding.

Dale K Over a year ago

@WannaKatana if you had posted the actual errors we could have help you understand them.

Collectives™ on Stack Overflow

Aggregate into custom groups

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related