For the first part of your problem you can do this :
//$answers taken from your first query posted
$answers->sum('value')
This sum() is a Collection method, the sum is made in PHP after getting your results back from the database.
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Basically i have two tables. answers and choices . In my choices table, I got a column choices.value which is has values 0-4. My current query like this
$answers = \DB::table('answers')
->join('choices','answers.choice_id','=','choices.choice_id')
->select('answers.user_id','choices.choice_id','choices.choice','choices.value')
->where('answers.user_id',\Auth::user()->id)
//->groupBy('answers.user_id')
->get();
My current response is like this
"choice_id": 2,
"choice": "I feel discourated about the future",
"value": 1
},
{
"user_id": 2,
"choice_id": 2,
"choice": "I don't enjoy things the way I used to",
"value": 1
},
{
"user_id": 2,
"choice_id": 2,
"choice": "I feel guilty a good part of time",
"value": 1
how do i add the values so that my result will be like this
"user_id":2,
"total_score":3
I tried doing DB::raw(SUM(choices.values)as score) but i get a large amount. I guess its adding all the choices values from the choices table and not in the answers.
My answers db which i only select the answers of the user = 2. I limit to 5
+---------+-------------+-----------+
| user_id | question_id | choice_id |
+---------+-------------+-----------+
| 2 | 1 | 2 |
| 2 | 2 | 2 |
| 2 | 3 | 2 |
| 2 | 4 | 2 |
| 2 | 5 | 2 |
+---------+-------------+-----------+
My choices table, I only select questions 1 and 2 and their choices.
-----------+-------------+-------+
| choice_id | question_id | value |
+-----------+-------------+-------+
| 1 | 1 | 0 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 1 | 3 |
| 1 | 2 | 0 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
+-----------+-------------+-------+
Also i want to make a new table named scores and the columns will be the result of what i want. I want to add the choices.values in the answer in every answers.user_id so that when i view the scores table, it will display the total score in every user or when the user finished answering all 21 questions because i only got 21 items it will automatically add in the scores table. Could I possibly do that?. Is it okay to add a value in the answers table based in the choice_id? That is what im thinking but I think its redundant since the choice_id is there already. Thanks in advance.
PS: Tried writing these queries but always get 441 which is the total value of all the choices in the choices table
SELECT answers.user_id,choices.choice_id,choices.value,COALESCE(sum(choices.value),0) as score FROM
`answers` JOIN `choices` ON choices.choice_id = answers.choice_id where
answers.user_id = 2
SELECT answers.user_id,choices.choice_id,choices.value,SUM(choices.value),0 as score FROM `answers`
join choices on choices.choice_id = answers.choice_id
where answers.user_id = 2
SELECT answers.user_id,choices.choice_id, sum(choices.value) from answers
JOIN `choices` ON choices.choice_id = answers.choice_id
group by answers.user_id
Please or to participate in this conversation.