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

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)

mysqlcount


Solution 1:[1]

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;

Solution 2:[2]

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JoelCrypto
Solution 2 JoelCrypto

Related Questions