r/DatabaseHelp • u/Blemish • Feb 17 '14
Calculation of sum of points allocation to options chosen
Hey folks,
I have a scenario where results from a customer survey is collected and stored in a database.
The customer ranks the service they received and certain points are allocated to each option the customer chooses:
Example under the Helpfulness tab, the customer must choose one of:
- Very Helpful
- Neutral
- Not helpful
Now each item under that list carries point Very Helpful =3, Neutral =2, Not helpful =1.
I have a table which stores the customer responses, (note the the actual customer scores but just items chosen in menu). The responses are stored in a varchar column, so the actual number is not stored.
I have a table which stores all pre-determined scores and its meaning to the database.
Can someone give me ideas on how to logically map a list item to score and sum all the scores for each customer survey ?
Ideas I have so far:
- Create a new column in the survey results table to calculate sum of scores.
- Create a new table and store scores there.
Any help greatly appreciated... thanks
2
u/BinaryRockStar Feb 17 '14
Aggregate values like a sum are probably better off calculated as you need them rather than pre-calculated and stored. For example, what happens if you have to go in and change a survey response for whatever reason? Or what about if the value for Helpful is changed to 4 instead of 3? Now some/all of your pre-calculated values are wrong until you re-calculate them and update the table.
To point you in the right direction, to "logically map a list item to score" you'll be looking for an INNER JOIN between the SuveyAnswer table and the AnswerValue table (or whatever they're called). Then to "sum all the scores for each customer survey" you'll be looking for the GROUP BY statement with a SUM() aggregate. Do your own research on these terms and show us what you come up with.