Counting Data in separate fields

[Prattaratt]

I have a table that has 5 fields; Each field contains a number 1 to N. I would like to count each occurence of a number, regardless of which field it's in. That's a total count of occurences for the number 1, regardless of whether it's in field1, field2, etc. using SQL.

 

[PHV]

SELECT theNumber, Count(*) AS CountOfNumber
FROM (
SELECT Field1 AS theNumber, 1 AS theField FROM yourTable
UNION SELECT Field2, 2 FROM yourTable
UNION SELECT Field3, 3 FROM yourTable
UNION SELECT Field4, 4 FROM yourTable
UNION SELECT Field5, 5 FROM yourTable
) AS U
GROUP BY theNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Prattaratt]

Ok, that gets me one number, now how about for each number? (I know I Specified a single number output in the original post, I apologize)

Ex:

fld1 fld2 fld3 fld4 fld5
1 2 3 4 5
2 3 4 5 6
3 4 5 6 7

Output:

Number Count
1 1
2 2
3 3
4 3
5 2
6 1

The simplest solution is the best!

 

[PHV]

how about for each number
I thought my suggestion counted all numbers in your table ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Prattaratt]

Just goes to show how little I use SQL. I misread the code to perform for just one number; however, I cut and pasted your solution and tried it, with innaccurate results. On a table with 77 occurences of the number 1 in the first field alone, it counted only one occurence in the whole table. I really have a hard time figuring out the interactions of the more complex statements in SQL, so I'm struggling to figure out why this doesn't work

The simplest solution is the best!

 

[PHV]

SELECT theNumber, Count(*) AS CountOfNumber
FROM (
SELECT Field1 AS theNumber, 1 AS theField FROM yourTable
UNION [!]ALL[/!] SELECT Field2, 2 FROM yourTable
UNION [!]ALL[/!] SELECT Field3, 3 FROM yourTable
UNION [!]ALL[/!] SELECT Field4, 4 FROM yourTable
UNION [!]ALL[/!] SELECT Field5, 5 FROM yourTable
) AS U
GROUP BY theNumber
[!]ORDER BY 1[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Prattaratt]

Okay, that worked beautifully! Thanks