COUNT the result from external table in Mysql always return 0 value-CodePudding

I tried to count rows with from 2 different table. the scenario is If status on tbl_task waiting, this job will not yet got the sales_id from tbl_sales. Once the the job id on process, the system will auto created the record in the tbl_sales and update the tbl_task for sales_id

tbl_tasks

     ---- --------- ---------- 
    | id | status  | sales_id |
     ---- --------- ---------- 
    | 1  | Waiting | NULL     |
     ---- --------- ---------- 
    | 2  | Done    | 19       |
     ---- --------- ---------- 
    | 3  | Process | 20       |
     ---- --------- ---------- 
    | 4  | Waiting | NULL     |
     ---- --------- ---------- 
    | 5  | Cancel  | NULL     |
     ---- --------- ---------- 




tbl_sales

     ---------- ----- 
    | sales_id | qty |
     ---------- ----- 
    | 18       | 20  |
     ---------- ----- 
    | 19       | 18  |
     ---------- ----- 
    | 20       | 2   |
     ---------- ----- 
    | 21       | 7   |
     ---------- ----- 
    | 22       | 9   |
     ---------- -----

This query :

SELECT count(a.sales_id) total_sales, SUM(CASE WHEN b.status = 'Waiting' THEN 1 ELSE 0 END) AS total_waiting FROM tbl_sales a
LEFT JOIN tbl_tasks b ON a.sales_id = b.sales_id

return waiting as 0 as follow:

 ------------- --------------- 
| total_sales | total_waiting |
 ------------- --------------- 
| 5           | 0             |
 ------------- ---------------

My expected result is:

 ------------- --------------- 
| total_sales | total_waiting |
 ------------- --------------- 
| 5           | 3             |
 ------------- ---------------

Any help will appreciate, I did also LEFT JOIN using subquery and got the wrong result as double for the total_sales (10)

CodePudding user response：

You are only retrieving sales id 19/20 that are not waiting.

You need a RIGHT JOIN here to get all elements from tasks.

CodePudding user response：

The issue here is you cannot join your tables because sales_id is not correlated. Therefore you can use (https://www.db-fiddle.com/f/mf59EiGksZEWQMbpfBwQji/0) :

SELECT (SELECT count(*) FROM tbl_sales) total_sales, COUNT(*) total_waiting FROM tbl_tasks WHERE `status`="Waiting";

However if you id were correlated (https://www.db-fiddle.com/f/2AN55gQFfE69zPoEXDnWSX/0), you could join your databases :

SELECT * FROM tbl_tasks a
INNER JOIN tbl_sales b ON b.sales_id=a.sales_id;
SELECT COUNT(*) total_sales, COUNT(IF(a.`status` = 'Waiting',1,NULL)) total_waiting FROM tbl_tasks a
INNER JOIN tbl_sales b ON b.sales_id=a.sales_id;