COUNT problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

COUNT problem

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-01-20 : 14:37:14

I have a table looks like:


Members                   Categoty_ID
A                              1
B                              1
C                              1
D                              1
A                              2
C                              2
D                              2
D                              3

I would like to generate a output that shows the count of members break down by the number of Category ID which they have..

For example,


 Category                              Count
Members with one category ID            1                            *only B has only one 
Members with 2 category ID              2                           **A and  C has 2 category ID
Members with 3+ category ID             1                           ***only D has 3+ category ID

How can I do this?

isanlu
Starting Member

7 Posts

Posted - 2005-01-20 : 15:13:08

--THIS IS HARD TO EXPLAIN BUT IT WORK USING YOUR EXAMPLE OF A,B,C,D

--CREATE A TABLE AND INSERT THE VALUES HERE
--YOU CAN POPULATE FROM YOUR SOURCE TABLE

CREATE TABLE #TEMP1 (
Member char(1),
CategoryID int
)

INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'A',1)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'B',1)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'C',1)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'D',1)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'A',2)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'C',2)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'D',2)
INSERT INTO #TEMP1 (Member,CategoryID)
VALUES ( 'D',3)

--FIND OUT HOW MANY CATEGORIES EACH MEMBER BELONGS TO AND PUT THAT INTO #TEMP2

SELECT Member,
Count(*) As CategoriesForMember
INTO #TEMP2

FROM #TEMP1
GROUP by Member

--GROUP BY THE NUMBER OF MEMBERS IN A CATEGORY AND CHANGE THE
--FIELD NAME TO RETURN THE ANSWER

SELECT 'Members with '+Convert(Char(1),CategoriesForMember)+' categoryID',

Count(*) As NumberOfMembers
FROM #TEMP2
GROUP BY CategoriesForMember

TRY RUNNING THIS SCRIPT FIRST TO SEE IF IT MAKES SENSE.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-20 : 16:10:12

select MemberCount, Count(MemberCount)
from (
-- Generates a list of Members and their count
select Member, count(*) MemberCount
from dbo.Junk
group by Member
) t
join (
-- List of numbers 1,2 and 3
select 1 Number union all
select 2 union all
select 3
) n123
on (n123.Number in (1, 2) and n123.Number = t.MemberCount)
or (n123.Number = 3 and t.MemberCount >= 3)
group by MemberCount
order by MemberCount

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources