CODEWITHSUNDEEP
mysqlsql
youanden
youanden

Reputation: 386

Join two tables and group by problems

I have two tables:

Problems

id | name

Responses

id | problem_id | user_id | value

I have the following statement.

SELECT 
`problems`.name, 
problem_id, 
sum(value) as knowledge, 
COUNT(*) as attempts FROM `responses`
LEFT JOIN `problems` ON `problems`.id = `responses`.problem_id
WHERE problem_id IS NOT NULL AND user_id = 4
GROUP BY problem_id

It produces a list like so:

| name      | problem_id | knowledge | attempts |
| NULL      | 1          | 6         | 6        |
| NULL      | 2          | 5         | 6        |
| NULL      | 3          | 4         | 6        |
| NULL      | 4          | 3         | 5        |

I'm missing something and I would be grateful if someone could help format to:

| name      | problem_id | knowledge | attempts |
| Problem A | 1          | 6         | 6        |
| Problem B | 2          | 5         | 6        |
| Problem C | 3          | 4         | 6        |
| Problem D | 4          | 3         | 5        |

Upvotes: 0

Views: 87

Answers (2)

peter.petrov
peter.petrov

Reputation: 39437

Try this.

select p.`name`, p.`id`, r.`user_id`, sum(r.`value`) as knowledge
from
`responses` r
join `problems` p on r.`problem_id` = p.`id`
where
r.`user_id` = 4
group by p.`name`, p.`id`

Upvotes: 2

Fabien Warniez
Fabien Warniez

Reputation: 2741

You cannot have in your SELECT something that is neither an aggregate function (like SUM or AVG), nor a variable part of the GROUP BY.

In your case, the name is neither.

Some DBMS might let you do it (MySQL), but the result is unpredictable.

Solution:

SELECT 
`problems`.name, 
problem_id, 
sum(value) as knowledge, 
COUNT(*) as attempts FROM `responses`
LEFT JOIN `problems` ON `problems`.id = `responses`.problem_id
WHERE problem_id IS NOT NULL AND user_id = 4
GROUP BY problem_id, problems.name

Upvotes: 1

Related Questions