How to display the total count of the last row by filestatus?

Question

I have to display the total count of the last row by filestatus.

tbl_bankdata

bank_id | b_orderno| b_bankname| lead_id
     1  | 01-01    | 1         |  1
     2  | 01-02    | 2         |  1
     3  | 02-01    | 3         |  2
     4  | 03-01    | 1         |  3

tbl_fileStatus

f_id | f_bankid| f_filestatus
  1  |      1  | 1
  2  |      2  | 1
  3  |      2  | 2
  4  |      1  | 2
  5  |      1  | 3
  6  |      3  | 2 
  7  |      3  | 3  

I have two tables tbl_bankdata and tbl_fileStatus. I am sending bank_id in the tbl_fileStatus as a f_bank_id.

Now I have to show the last f_bankid count.

For example, I have to fetch the count where f_filestatus=1. so my output will be 0. Why 0 because f_bankid 1 and 2 have a f_filestatus=1 but f_bankid 1 and 2 have the last row with f_filestatus the 3 and 2.

If I have to count f_filestatus=2 then I will get the output 1 and if count f_filestatus=3 then the output will be 2. why 2 because f_bank_id 1 have f_filestatus 3 and f_bank_id 3 have f_filestatus 3

This is my query

select (
    SELECT COUNT(f_id) 
    FROM tbl_fileStatus 
    WHERE f_filestatus=1 and f_id IN (
        SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid
    )
) as tcount

Would you help me out with this issue?

After suggested by @forpas

SELECT (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  1 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount1,
   (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  2 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount2,
   (SELECT Count(DISTINCT f_bankid)
    FROM   tbl_filestatus t
    WHERE  3 = (SELECT f_filestatus
                FROM   tbl_filestatus
                WHERE  f_bankid = t.f_bankid
                ORDER  BY f_id DESC
                LIMIT  1)) AS tcount3  

Answer 1

Use a correlated subquery:

SELECT COUNT(DISTINCT f_bankid) AS tcount
FROM tbl_fileStatus t
WHERE ? = (SELECT f_filestatus FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)

Replace ? with the f_bankid you search for.
See the demo.

In MySql 8.0+ you can use FIRST_VALUE() window function:

SELECT COUNT(*) AS tcount
FROM (
  SELECT DISTINCT f_bankid, 
         FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
  FROM tbl_fileStatus
) t
WHERE f_filestatus = ?

See the demo.

If you want results for all f_filestatus in 1 row:

SELECT
  SUM(f_filestatus = 1) AS tcount1,
  SUM(f_filestatus = 2) AS tcount2,
  SUM(f_filestatus = 3) AS tcount3
FROM (
  SELECT t.f_bankid, t.f_filestatus
  FROM tbl_fileStatus t
  WHERE t.f_id = (SELECT f_id FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)
) t

See the demo.
Results:

> tcount1 | tcount2 | tcount3
> ------: | ------: | ------:
>       0 |       1 |       2