How do you group count, order by and give where clauses to the join table?

Question

So I have a table relationship schema like this:
t_report (as a parent)

╔════╦═════════╗
║ id ║ id_icr  ║
╠════╬═════════╣
║ 1  ║ 2209001 ║
╠════╬═════════╣
║ 2  ║ 2209002 ║
╚════╩═════════╝

t_report_detail (refer to t_report)

╔════╦═══════════╦═══════════╗
║ id ║ id_report ║ name      ║
╠════╬═══════════╬═══════════╣
║ 1  ║ 1         ║ Process 1 ║
╠════╬═══════════╬═══════════╣
║ 2  ║ 1         ║ Process 2 ║
╠════╬═══════════╬═══════════╣
║ 3  ║ 1         ║ Process 3 ║
╠════╬═══════════╬═══════════╣
║ 4  ║ 2         ║ Process 1 ║
╠════╬═══════════╬═══════════╣
║ 5  ║ 2         ║ Process 2 ║
╚════╩═══════════╩═══════════╝

t_report_details (refer to t_report_detail)

╔════╦══════════════════╦════════╗
║ id ║ id_report_detail ║ status ║
╠════╬══════════════════╬════════╣
║ 1  ║ 1                ║ 1      ║
╠════╬══════════════════╬════════╣
║ 2  ║ 1                ║ 3      ║
╠════╬══════════════════╬════════╣
║ 3  ║ 2                ║ 1      ║
╠════╬══════════════════╬════════╣
║ 4  ║ 2                ║ 2      ║
╠════╬══════════════════╬════════╣
║ 5  ║ 3                ║ 1      ║
╠════╬══════════════════╬════════╣
║ 6  ║ 3                ║ 2      ║
╠════╬══════════════════╬════════╣
║ 7  ║ 4                ║ 1      ║
╠════╬══════════════════╬════════╣
║ 8  ║ 4                ║ 4      ║
╠════╬══════════════════╬════════╣
║ 9  ║ 5                ║ 1      ║
╚════╩══════════════════╩════════╝

What I want is:

1. Find out how many processes are running now in table t_report_detail
2. By taking what is their last status condition in t_report_details. (1) Waiting, (2) Accepted, (3) Canceled, (4) Rejected
3. I want the count results to be presented, only those that have Status (1) Waiting, (2) Accepted.

I hope my output is like this:

╔════╦═════════╦═════════╗
║ id ║ id_icr  ║ ongoing ║
╠════╬═════════╬═════════╣
║ 1  ║ 2209001 ║ 2       ║
╠════╬═════════╬═════════╣
║ 2  ║ 2209002 ║ 1       ║
╚════╩═════════╩═════════╝

I have tried with this code and stuck, because the ORDER BY t_report_details.id DESC table always doesn't work, so I can't get the last state condition from t_report_details and put it in the WHERE t_report_details.id_status IN(1,2) condition clause

Query

SELECT 
COUNT(t_report_details.id_report_detail) AS ongoing,
t_report.*
FROM t_report
JOIN t_report_detail ON t_report.id = t_report_detail.id_report
JOIN t_report_details ON t_report_detail.id = t_report_details.id_report_detail
WHERE t_report_details.id_status IN(1,2)
GROUP BY t_report_detail.id_report
ORDER BY t_report_details.id DESC;

And I'm also confused about how to use order by and where to produce the output as I expect..

Please help, thank you

Answer 1

With your current query:

SELECT  
    COUNT(t_report_details.id_report_detail) AS ongoing,
    t_report.* 
FROM t_report 
JOIN t_report_detail ON t_report.id = t_report_detail.id_report 
JOIN t_report_details ON t_report_detail.id= t_report_details.id_report_detail 
WHERE t_report_details.id_status IN(1,2) 
GROUP BY t_report_detail.id_report 
ORDER BY t_report_details.id DESC;

By applying the t_report_details.id_status IN(1,2) filter in the where clause before determining your last status, you are more likely to miss details with other statuses that would have been their last status.

Sub queries may be used to help with finding the most recent process status before filtering them on whether they are ongoing. I have included two approaches:

1. Instead of using an order by, you may use MAX (See Query 1 and Query 2) to find the most recent status in a sub query and join based on this.

2. You may also use ROW_NUMBER (See Query 3) in later mysql versions (8+). Ordering the data by the id in descending order for groups/partitions determined by id_report_detail will provide the most recent. In this example we add an additional filter trds_last_status.rn=1 to get the most recent row which will have a row number of 1.

NB:

1. Query 1 was included to give an idea of what the filtered data looks like before it is aggregated.
2. Moreover, left joins were used to ensure that we have all the initial records from t_report and that 0 values will be reported if there are no ongoing processes.
3. COUNT(DISTINCT trds_last_status.id_report_detail) was used to ignore duplicates

Query #1

SELECT
    tr.id,
    tr.id_icr,
    trds_last_status.id_report_detail,
    trds_last_status.status
FROM
    t_report tr
LEFT JOIN
    t_report_detail trd ON tr.id = trd.id_report
LEFT JOIN (
    SELECT id_report_detail,  status
    FROM t_report_details 
    WHERE id in (
        SELECT MAX(id)
        FROM t_report_details
        GROUP BY id_report_detail
    )
) trds_last_status ON trd.id = trds_last_status.id_report_detail AND
                      trds_last_status.status IN (1,2)
;

id	id_icr	id_report_detail	status
1	2209001	3	2
1	2209001	2	2
1	2209001
2	2209002	5	1
2	2209002

---

Query #2

SELECT
    tr.id,
    tr.id_icr,
    COUNT(DISTINCT trds_last_status.id_report_detail) as ongoing
FROM
    t_report tr
LEFT JOIN
    t_report_detail trd ON tr.id = trd.id_report
LEFT JOIN (
    SELECT id_report_detail,  status
    FROM t_report_details 
    WHERE id in (
        SELECT MAX(id)
        FROM t_report_details
        GROUP BY id_report_detail
    )
) trds_last_status ON trd.id = trds_last_status.id_report_detail AND
                      trds_last_status.status IN (1,2)
GROUP BY
    tr.id,
    tr.id_icr;

id	id_icr	ongoing
1	2209001	2
2	2209002	1

---

Query #3

SELECT
    tr.id,
    tr.id_icr,
    COUNT(DISTINCT trds_last_status.id_report_detail) as ongoing
FROM
    t_report tr
LEFT JOIN
    t_report_detail trd ON tr.id = trd.id_report
LEFT JOIN (
    SELECT 
        id_report_detail,  
        status, 
        ROW_NUMBER() OVER (
            PARTITION BY id_report_detail
            ORDER BY id DESC
        ) rn
    FROM t_report_details 
) trds_last_status ON trd.id = trds_last_status.id_report_detail AND
                      trds_last_status.status IN (1,2) AND
                      trds_last_status.rn=1
GROUP BY
    tr.id,
    tr.id_icr;

id	id_icr	ongoing
1	2209001	2
2	2209002	1

---

View working demo on DB Fiddle