Aggregate Query Question - 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)

Aggregate Query Question

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2008-03-06 : 13:55:16

I have several columns of integers that I would like to create an aggregate table that counts all the times an integer shows up in each column.

ColA, ColB, ColC, ColD
1, 3, 4, 5
2, 4, 4, 1
3, 2, 5, 6
6, 1, 1, 2

the above would then become:

id/A/B/C/D
1, 1, 1, 1, 1
2, 1, 1, 0, 1
3, 1, 1, 0, 0
4, 0, 1, 2, 0
5, 0, 0, 1, 1
6, 1, 0, 0, 1

Also, I would like to be able to select all distinct numbers from ABCD to know which numbers I need to count as I don't know what the range of numbers is going to be in columns ABCD.

Any help is greatly appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 14:12:49

What have you tried so far?

legacyvbc
Starting Member

37 Posts

Posted - 2008-03-06 : 14:46:36

I am just looping through numbers in the front end and storing the results in an array which takes too long.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 15:05:32

FYI -- to select all distinct numbers from all 4 columns, you write:

select ColA as N from tbl
union
select ColB from tbl
union
select ColC from tbl
union
select ColD from tbl

The union operator automatically removes duplicates.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 18:59:12

[code]DECLARE @Table TABLE (ColA INT, ColB INT, ColC INT, ColD INT)

INSERT @Table
SELECT 1, 3, 4, 5
UNION ALL SELECT 2, 4, 4, 1
UNION ALL SELECT 3, 2, 5, 6
UNION ALL SELECT 6, 1, 1, 2

SELECT
ID.ID,
COALESCE(A.ACount, 0) AS A,
COALESCE(B.BCount, 0) AS B,
COALESCE(C.CCount, 0) AS C,
COALESCE(D.DCount, 0) AS D
FROM
(
SELECT ColA AS ID FROM @Table
UNION
SELECT ColB FROM @Table
UNION
SELECT ColC FROM @Table
UNION
SELECT ColD FROM @Table
) ID
LEFT OUTER JOIN
(
SELECT ColA, COUNT(*) AS ACount
FROM @Table
GROUP BY ColA
) AS A
ON ID.ID = A.ColA
LEFT OUTER JOIN
(
SELECT ColB, COUNT(*) AS BCount
FROM @Table
GROUP BY ColB
) AS B
ON ID.ID = B.ColB
LEFT OUTER JOIN
(
SELECT ColC, COUNT(*) AS CCount
FROM @Table
GROUP BY ColC
) AS C
ON ID.ID = C.ColC
LEFT OUTER JOIN
(
SELECT ColD, COUNT(*) AS DCount
FROM @Table
GROUP BY ColD
) AS D
ON ID.ID = D.ColD[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources