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:
| intRecID | nvchName | intIceCreamID |
|
|
||
| 1 | Jake | 1 |
| 2 | Jennifer | 2 |
| 3 | Kitty | 3 |
Here is the table that contains the corresponding values for intIceCreamID.
| intRecID | nvchFlavor | |
|
|
||
| 1 | Vanilla | |
| 2 | Chocolate | |
| 3 | Strawberry | |
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):
| VanillaCount | ChocolateCount | StrawberryCount |
|
|
||
| 1 | 1 | 1 |
You can easily get a total of the records, replacing COUNT with SUM in the view. You could also do them all in one.