SQL Server - Nested Aggregation Functions

Question

I want to do a nested aggregation, is this possible? SQL Server 2012 is returning an error when I try to do the following:

SELECT SUM(COUNT(memberID)) FROM table

The situation I have is the following: I have members who have the same member ids as their dependents in the same table. I want to get the count of the the members and their dependents based on the memberID, however, I want the count in a column next to the main enrollee which is identified by another column as an e.

SELECT memberID, memberName, COUNT(memberID)
FROM table
WHERE memberRole = 'e'

The above would return 1 for all results, so I was thinking if I count the memberIds, then sum them would work but that returned an error, Am I doing something wrong? What is the best way to reach this porblem

user3175748 · Accepted Answer · 2014-04-16 17:27:54Z

2

Your original query was correct, with a slight change:

SELECT MemberID, MemberName, (SELECT COUNT(MemberID) FROM table WHERE MemberID = M.MemberID GROUP BY MemberID) AS MemberCount
FROM table M
WHERE M.MemberRole = 'E'

answered Apr 16, 2014 at 17:27

user3175748

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

1 Comment

user3221917 Over a year ago

Awesome! I did not think of using sub queries. Thank you Sir.

Reza · Accepted Answer · 2014-04-16 17:13:24Z

0

try this:

SELECT memberID, memberName, Sum(CNT) From
(
  SELECT memberID, memberName, COUNT(memberID) CNt
  FROM table
  WHERE memberRole = 'e'
) t
group by memberID, memberName

answered Apr 16, 2014 at 17:13

Reza

20.1k18 gold badges102 silver badges177 bronze badges

Collectives™ on Stack Overflow

SQL Server - Nested Aggregation Functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related