[RESOLVED] Getting sum of table cell values from a large table

lalloo

I have a MySQL table that contains data from a survey. The survey has about 30 questions and hence at least 30 fields in the table. All the questions are multiple choice and have 5 to 10 possible answers. Now, to generate a report that contains totals of each item (for example if a question "favorite sport" has 10 possible answers, S1, S2,...,S10, I need to know how many people answered S1 as their favorite sport, how many answered S2, and so on. And this I need to do for each field for any given date range.

I am not sure what would be the most efficient way to generate this report. One possibility is to run just one query (SELECT * from Survey WHERE date > abc AND date < xyz), and then run counters inside the while loop for each possible answer. The other way would be to run separate queries for each possible answer (e.g., SELECT sport from Survey WHERE sport='s1') and get [FONT="Courier New"]mysql_num_rows[/FONT]. This seems to be a very long process and just too many queries. Is there any other way to generate this report in a better, faster and more efficient way? Thanks.

A sample report would have info like this:
Q1:
a1=100, b1=120, c1=33, d1=50, e1=0, blank1=30

Q2.:
a2=3, b2=110, c2=12, d2=98, e2=50, blank2=60

...

cgraz

Could you post your database structure and some insert statements?

tomhath

You have a column for each question? You made it much more difficult by setting it up that way. It would've been easier if the table had just two columns, question and answer (along with other survey information such as person and date).

I can't think of an easy way to get the report you want in a single query. You'll have to query each column in a loop in php, and build the output from the values you get back:

  select sport, count(*) as numanswers from Survey group by sport

lalloo

Thanks, tomhath.

I have changed the structure based on your comments. Now I have 2 tables -- first with 3 columns (userid, question, answer) and the second table contains userid, logdate and status. Now when the survey form is submitted, it populates as many rows in table 1 as the questions are answered. While table 2 stores the info on only one row. To get the count, I am using a query like this:

SELECT question, answer, count(*) cnt  FROM tbl1, tbl2 WHERE (tbl1.userid=tbl2.userid AND logdate >= '$fromdate' AND logdate <= '$todate' AND tbl1.question = '$question') GROUP BY question, answer ORDER BY question, count(*) DESC;

This query is giving me the proper count for each answer. However, I also need to generate a master report that will have the userid, date and all the answers from the user in just one row (for each userid). What would be the most efficient and quick way to join the tables to get the master report? Thanks.

tomhath

Depends on what you're using to create the report. Assuming php, I'd just fetch all the rows and fill an HTML table. Loop through the rows, every time the userid changes, start a new HTML table row. Use the value in tbl1.question to determine where the answer is displayed. That's a common way of displaying data like this, write the code once and it'll be easy next time.

SELECT tbl2.userid, tbl2.logdate, tbl1.question, tbl1.answer  

FROM tbl1, tbl2 
WHERE (tbl1.userid=tbl2.userid
 AND logdate >= '$fromdate'
 AND logdate <= '$todate' 
order by tbl2.userid, tbl1.question

lalloo

Thank you very much, tomhath.

bradgrafelman

Don't forget to mark this thread resolved (if it is).