SQL: Calculate the sum of a column based on 2 other columns in where clause (Magento 1.9.3.6)

Question

I've got a bit of an interesting problem: I have a database where questions and answers are saved, each question/answer combo is saved as a separate row, sharing a participation id between each entry. The data looks like this:

id | participation_id | q | a
--------------------------------
1  | 1                | 1 | q1a1
2  | 1                | 2 | 10
3  | 2                | 1 | q1a2
4  | 2                | 2 | 15
5  | 3                | 1 | q1a1
6  | 3                | 2 | 10

The id is just an auto-increment primary key, the participation_id is the link between question 1 and 2 (only 2 questions), q is question number and a is the given answer for that question. Both q and a are selects, so no custom input.

I'd like to get the sum of all answers (a) where question number (q) = 2 and where the same answer for question 1 has been chosen.

Example from above

Desired result for people who answered the first question with q1a1: 20

Desired result for people who answered the first question with q1a2: 15

So basically a total of all ansers for question 2 who entered the same value on the first question, possibly based on participation_id?

I had the following code, but obviously it didn't work the way I'd like it to:

$select = $read->select()
    ->from('q_and_a', 'SUM(a)')
    ->where('q = 1')
    ->where('a = ?', 'q1a1')
;

$sum = $readConn->fetchOne($select);

This tries to get the sum of all answers where question 1 is the same, not the sum of the answers for question 2 where question 1 is the same (based on participation_id?).

I hope this is somewhat clear, this wasn't an easy one to explain (or figure out). Feel free to improve/correct this question to make more sense, if needed. And if you know the answer, please let me know.

Answer 1

Okay, so I didn't get it to work with Magento's built-in query builder, but I did find a solution: just use direct sql and always get all the totals as array and based on an optional parameter return the correct (or all) value(s).

Here's the code that does it:

public function getQandA($question = null)
{
    $readConn = Mage::getSingleton('core/resource')->getConnection('core_read');

    $sql = "SELECT q1.a AS question, SUM(q2.a) as total FROM q_and_a q1 INNER JOIN q_and_a q2 ON q2.participation_id = q1.participation_id AND q2.q = 2 WHERE q1.q = 1 GROUP BY q1.a";

    $rows = $readConn->fetchAll($sql);

    if ($question) {
        $returnValue = false;

        foreach ($rows as $row) {
            if ($row['question'] === $question) {
                $returnValue = $row['total'];
            }
        }

        if ($returnValue) {
            return $returnValue;
        } else {
            return 0;
        }
    }

    return $rows;
}

The only problem is you can only have one participation_id per question-answer combo series (in this case 2 questions and answers: q1-a1 and q2-a2), but the number of times anyone can enter is limited to only 1 entry, so no duplicate participation_id's should be found anyways.

Hopefully anyone stumbling on this (or a similar) problem in the future has some use whith this answer.