I have been doing quite a few surveys recently and one of the tasks involved is mining the responses for data. Here's a neat trick I learned for totalling and counting responses using sql rather than programmatically on the front end.
Let's say you have a table of customers (tblCustomers) that houses the name of each person and the flavor of ice cream each customer prefers:
Here is the table that contains the corresponding values for intIceCreamID.
As you can see, I like vanilla, Jennifer likes chocolate and the cat likes strawberry.
To count the number of people that like each flavor, you could use the following view:
CREATE VIEW sv_FlavorTotals AS SELECT (SELECT COUNT(intFlavorID) FROM tblCustomers WHERE intFlavorID = 1) AS VanillaCount, (SELECT COUNT(intFlavorID) FROM tblCustomers WHERE intFlavorID = 2) AS ChocolateCount, (SELECT COUNT(intFlavorID) FROM tblCustomers WHERE intFlavorID = 3) AS StrawberryCount GO
The output would look like this (assuming we only had the three records shown above):
You can easily get a total of the records, replacing COUNT with SUM in the view. You could also do them all in one.