b4dQuetions b4dQuetions - 6 months ago 16
MySQL Question

how to fetch this query using 2 table

Hy I have 2 table

1.application

id | name | status


====================


1 | morvick | complete


2 | siti | prosess


3 | boby | complete

`

2.application_test

id | application_id | test_id | result


======================================


1 | 1 | 1 | 70


2 | 1 | 2 | 80


3 | 1 | 3 | 90


4 | 2 | 1 | 60


5 | 2 | 2 | 80


6 | 2 | 3 | 70


7 | 3 | 1 | 90


8 | 3 | 2 | 70


9 | 3 | 3 | 60


10| 3 | 4 | 80


my Question is :

==================

1. how to find the maximum value at each test_id

2. how I can to get or total applicant_id where status complete

for example to be like this :

test_id | result_max | total_applicant_status(complete)


1 | 90 | 2


2 | 80 | 2


3 | 90 | 2


4 | 80 | 1


Answer
SELECT MAX(value) FROM table WHERE test_id = 1;

or perhaps SELECT value, test_id FROM table ORDER BY value DESC;

and for the next part, this may give what you want.

SELECT at.test_id, MAX(at.result), COUNT(IF(status='complete', 1, 0)) FROM application a LEFT JOIN application_test at ON a.id = at.application_id GROUP BY application_id;