SQL: Totaling A Column Containing Varying Values

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.