Select count of same entries in different tables

Animate It 0
Viewed 26

I got 3 tables


| ID   | Name   |
|:---- |:------:| 
| 1    | Brie   | 
| 2    | Ray    | 
| 3    | James  | 

Table2

| ID   | Q_id   | Q_no  | ans |
|:---- |:------:| -----:|----:|
| 1    | 2304.  | 1     |  A  |
| 1    | 2304.  | 2     |  A  |
| 1    | 2305.  | 1     |  C  |
| 2    | 2304.  | 2     |  A  |
| 2    | 2305.  | 1     |  C  |
| 3    | 2304.  | 1     |  A  |
| 3    | 2305.  | 2     |  D  |

Table3
 | Q_id   | Q_no  | correct_ans |
 |:------:| -----:|------------:|
 | 2304.  | 1     |  A          |
 | 2304.  | 2     |  B          |
 | 2305.  | 1     |  C          |
 | 2305.  | 2     |  D          |

I need to print a table with ID, name and count of ans in table 2 where ans matches with correct answer from table 3

| ID   | Name   | ans_count  |
|:---- |:------:| ----------:|
| 1    | Brie   |   2        |
| 2    | Ray    |   1        |
| 3    | James  |   2        |

Select t1.ID, Name, count(t2.ans) as ans_count
from Table1 t1
join Table2 t2 on t1.ID=t2.ID
join Table3 t3 on t2.Q_id=t3.Q_id
where t2.ans=t3.correct.ans and t2.q_no=t3.q_no
group by t1.ID
order by t1.ID

Where am I doing it wrong? Sorry I am new to SQL.

count join mysql database sql
2 Answers

Its still not clear what you are after, but this corrects the obvious issue with the query.

Select t1.ID, Name, count(*) as ans_count
from Table1 t1
join Table2 t2 on t1.ID=t2.ID
join Table3 t3 on t2.Q_id=t3.Q_id
where t2.ans=t3.correct.ans
group by t1.ID, t1.Name  ///<---------------
order by t1.ID

I think you need to group by Id and Name

Rohit Gupta0

You should always link the tables, with all colums that match

AND the GROUP By should contain all columns that have no aggregation function

SELECT t1.ID,t1.`Name`,COUNT(*) as correct_answers
FROM table1 t1 
  JOIN table2 t2 ON t1.ID = t2.ID
  JOIN table3 t3 ON t2.`Q_id` = t3.`Q_id` AND  t2.`Q_no` = t3.`Q_no`
WHERE t3.`correct_ans` = t2.`ans`
GROUP BY t1.ID,t1.`Name`
order by t1.ID
ID Name correct_answers
1 Brie 2
2 Ray 1
3 James 2

fiddle

nbk0
Related