CODEWITHSUNDEEP
mysql
Ender
Ender

Reputation: 27283

MySQL Create Summary Table?

I have a table of answers to questions:

table questionAnswers:
    user VARCHAR
    question VARCHAR
    answerValue VARCHAR

Important: Users can post multiple answers to questions

I want to collect how many questions a user has answered into a table:

table users
    user VARCHAR
    questionsAnswered INT

Important: Has to ignore multiple answers to the same question

Is there a single query that can automatically update the questionsAnswered column in the users table?

Upvotes: 2

Views: 1489

Answers (1)

unutbu
unutbu

Reputation: 879271

UPDATE users AS u 
SET questionsAnswered = (
    SELECT COUNT(DISTINCT question) 
    FROM questionAnswers AS q 
    WHERE q.user=u.user)

This assumes that all the users in questionsAnswered already have an entry in users. (This will update users, but it won't insert any new rows.)

Upvotes: 3

Related Questions