Get count for row data across all columns

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)

Get count for row data across all columns

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-08-18 : 18:58:06

I have a table that contains an ID column, along with 5 class columns (and a table with class names). These class columns contain Class ID's. I need to return all of the class names along with the number of times it appears in any of the buckets.

So, given the data below, I am expecting the following results:

ClassID Count
---------------------
1 4
2 3
3 4
4 4
5 4
6 1
7 1
8 3
9 1

Any Help is appreciated. Thanks in advance.

----------------------------------------------------------------------

CREATE TABLE [atsc] (
[AttendeeScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[Class1ID] [int] NOT NULL ,
[Class2ID] [int] NOT NULL ,
[Class3ID] [int] NOT NULL ,
[Class4ID] [int] NOT NULL ,
[Class5ID] [int] NOT NULL
)
GO

INSERT atsc (Class1ID, Class2ID, Class3ID, Class4ID, Class5ID) SELECT 1,3,4,5,8
INSERT atsc (Class1ID, Class2ID, Class3ID, Class4ID, Class5ID) SELECT 3,5,4,8,9
INSERT atsc (Class1ID, Class2ID, Class3ID, Class4ID, Class5ID) SELECT 1,2,3,5,7
INSERT atsc (Class1ID, Class2ID, Class3ID, Class4ID, Class5ID) SELECT 4,1,2,3,5
INSERT atsc (Class1ID, Class2ID, Class3ID, Class4ID, Class5ID) SELECT 8,6,2,1,4
go

CREATE TABLE [Classes] (
[ClassID] [int] NOT NULL ,
[CName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO

INSERT Classes SELECT 1, 'Class1'
INSERT Classes SELECT 2, 'Class2'
INSERT Classes SELECT 3, 'Class3'
INSERT Classes SELECT 4, 'Class4'
INSERT Classes SELECT 5, 'Class5'
INSERT Classes SELECT 6, 'Class6'
INSERT Classes SELECT 7, 'Class7'
INSERT Classes SELECT 8, 'Class8'
INSERT Classes SELECT 9, 'Class9'
INSERT Classes SELECT 10, 'Class10'
go

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 03:40:18

[code]
SELECT [ClassID],
[Count] = SUM([Count])
FROM
(
SELECT [ClassID] = Class1ID,
[Count] = COUNT(*)
FROM atsc
GROUP BY Class1ID
UNION ALL
SELECT [ClassID] = Class2ID,
[Count] = COUNT(*)
FROM atsc
GROUP BY Class2ID
UNION ALL
SELECT [ClassID] = Class3ID,
[Count] = COUNT(*)
FROM atsc
GROUP BY Class3ID
UNION ALL
SELECT [ClassID] = Class4ID,
[Count] = COUNT(*)
FROM atsc
GROUP BY Class4ID
UNION ALL
SELECT [ClassID] = Class5ID,
[Count] = COUNT(*)
FROM atsc
GROUP BY Class5ID
) X
GROUP BY [ClassID]
ORDER BY [ClassID]
[/code]
Kristen

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-08-19 : 11:06:24

Kristen, thanks so much. This worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources