Total of members

[qjlee]

I have a table called Groups contains two columns, one called GroupID, the
other called Memberid. Groupid is the ID for the Group for each members
(identified by Memberid).

I need to write a script to total the number od members in each group and
the total number for all groups.

Can somebody help me with that?

Thanks,

[Aaron Bertrand [SQL Server MVP]]

SELECT GroupID, COUNT(MemberID)
FROM dbo.Groups
GROUP BY GroupID
WITH ROLLUP;

"qjlee" <qj...@discussions.microsoft.com> wrote in message
news:12B822FF-B07A-47F4...@microsoft.com...

[Plamen Ratchev]

Two ways, depends if you need the total count in a column or as additional
row:

-- As row
SELECT groupid AS groupid,
COUNT(*) AS members_cnt
FROM Groups
GROUP BY groupid
WITH ROLLUP;

-- As column
SELECT groupid,
COUNT(*) AS members_cnt,
(SELECT COUNT(*) FROM Groups) AS groups_cnt
FROM Groups
GROUP BY groupid;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

One of the many reasons we ask that you post DDL is that we do not6
like to guess about your data. Since GROUP is a reserved word, let's
make that clubs instead.

-- one member in many clubs?
CREATE TABLE Clubs
(club_id CHAR(10) NOT NULL,
member_id CHAR(10) NOT NULL,
PRIMARY KEY (club_id, member_id));

--or can I only join one club?
CREATE TABLE Clubs
(club_id CHAR(10) NOT NULL,
member_id CHAR(10) NOT NULL PRIMARY KEY);

So we might write this for the first one :

SELECT X.club_id, X.membership_cnt
FROM (SELECT club_id, COUNT(member_id)
FROM Clubs
UNION
SELECT 'All of them", COUNT(DISTINCT member_id)
FROM Clubs) AS X(club_id, membership_cnt)

and this for the second:

SELECT club_id, COUNT(member_id) AS membership_cnt
FROM Clubs
GROUP BY club_id WITH ROLLUP;

Now think about NULLs and those complications.

[qjlee]

I think it should be as row.

However, Is there a way to to add a name to that row because this way the
row name is Null.

Thanks,

[--CELKO--]

>> Is there a way to to add a name to that row because this way the row name is NULL.

SELECT COALESCE (club_name, 'All of Them') AS club_name,
COUNT(*) AS members_cnt
FROM Clubs
GROUP BY club_name
WITH ROLLUP;

You might want to look at the GROUPING() function that is part of the
super grouping features.

[Plamen Ratchev]

Yes, use the GROUPING function. It returns 1 when the row is added by
ROLLUP.

SELECT CASE WHEN GROUPING(groupid) = 1
THEN 'Total All Groups'
ELSE CAST(groupid AS VARCHAR(30))
END AS groupid,

COUNT(*) AS members_cnt
FROM Groups
GROUP BY groupid
WITH ROLLUP;

HTH,

Plamen Ratchev
http://www.SQLStudio.com