Question 113502 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select count of same entries in different tables

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search