But the test_id, test_name and test_code don’t make sense in that context
Apr 8, 2022
4
Level 5
mysql query to get count
i have tables like below,
tests
id, test_name, test_code, course_id
upload_answers
id, test_id, file_name
Now i want to fetch total upload_answers count for a particular course.
Example: if course_id = 1
then i want to fetch total upload_answers which matches course_id = 1 tests
i tried like below,
SELECT t.test_id,t.test_name,t.test_code,
(SELECT COUNT(test_id) FROM upload_answers WHERE test_id = t.test_id ) AS upload_count
FROM tests t WHERE t.course = $courseid
Above query displays each tests with upload_answers count.
instead i want to get total upload_answers including of all tests
Right now i am getting response like below,
0 => {#1392 ▼
+"test_id": 6316
+"test_name": "Test 1"
+"test_code": "T-6313"
+"upload_count": 5
}
1 => {#1394 ▼
+"test_id": 6317
+"test_name": "Test 2"
+"test_code": "T-6314"
+"upload_count": 6
}
2 => {#1388 ▼
+"test_id": 6318
+"test_name": "Test 3 (Theme-Based)"
+"test_code": "T-6315"
+"upload_count": 5
}
But output should be like below,
upload_count : 16
Thank you
Level 104
SELECT COUNT(test_id) AS upload_count
FROM tests t
LEFT JOIN upload_answers ON test_id = t.test_id
WHERE t.course = $courseid
Be careful about SQL injection
Please or to participate in this conversation.