SQL: Counting many columns and suming the result?

[SiD the Turtle]

A possibly daft one for you. Consider the following table with fields a, b, c, d and e:

a b c d e
---------
a e t s p
e a v s o
a v t o s

Each of the five fields contains the same type of data as the other four. What I want to do is count the number of occurrences of a value in each column and then sum the result. Counting column a would be 'SELECT COUNT(a) FROM table GROUP BY a'. I think I could do this statement for all five columns and UNION them together but it looks messy. Is there an easier way I haven't thought of?

 

[GravyMonster]

is this MSSQL? A stored procedure would be the way forward - run your 4 statements, return the counts to variables, sum the variables and return the output.

 

[SiD the Turtle]

MyQL unfortunately, though a quick Google suggests MySQL 5 supports stored procedures, I'll have a look.

I do spend a lot of time thinking 'I could do this faster in SQL 2005'

 

[GravyMonster]

I've never worked with MySQL, being an MS bitch, but here's how I'd do it, hopefully it should transfer pretty much verbatim.

CREATE PROCEDURE spGetTotalOccurences
	@Total int OUTPUT
AS
	DECLARE @TotalA int
	DECLARE @TotalB int
	DECLARE @TotalC int
	DECLARE @TotalD int

BEGIN
	SELECT @TotalA = COUNT(A) FROM Table GROUP BY A
	SELECT @TotalB = COUNT(B) FROM Table GROUP BY B
	SELECT @TotalC = COUNT(C) FROM Table GROUP BY C
	SELECT @TotalD = COUNT(D) FROM Table GROUP BY D

	SET @Total = (@TotalA + @TotalB + @TotalC + @TotalD)
END

 

[Rossmac]

That should work fine.

 

[voodooflux]

I probably haven't under the original question correctly, but does that stored procedure give the intended result? If you run it against the test data you get a result of 5. Are you after the number of occurences of a single value, say 'a', or the number of occurences of all the values?

If you run the line...

SELECT COUNT(A) FROM Table GROUP BY A

...against the test data quoted then this gives a rowset with 2 values: 2 and 1 (i.e. there are two occurences of 'a' and one of 'e') whereas the line...

SELECT @TotalA = COUNT(A) FROM Table GROUP BY A

...assumes that only one value is returned.

Apologies if I've completely misunderstood the problem/solution!