'MYSQL: Display data using mathematical method and group by date

I have two tables, which are qrc_factory_bay and qrc_loading_bay_tracking. Table qrc_factory_bay is stored how many bays (in the bay and outside the bay) are in a factory. Table qrc_loading_bay_tracking is to record the bay that is used daily. Below is the query that i used:

SELECT qrc_loading_bay_tracking.date_in, 
    COUNT(qrc_loading_bay_tracking.id) AS total_bay_used, 
    FLOOR(SUM(qrc_factory_bay.in_bay + qrc_factory_bay.out_bay)) AS total_loading_bay 
FROM qrc_factory_bay 
INNER JOIN qrc_loading_bay_tracking ON qrc_loading_bay_tracking.factory_bay_id = qrc_factory_bay.id 
WHERE qrc_loading_bay_tracking.date_in >= ( CURDATE() - INTERVAL 2 DAY ) 
    AND qrc_factory_bay.status = 1 
GROUP BY qrc_loading_bay_tracking.date_in

The results as shown below:

enter image description here

The highlighted value is wrong. The supposed should be 78. 78 is the total loading bay counted at table qrc_factory_bay. Thus, can I know what is the problem? Can anyone fix the query?

Thank you.

mysql


Sources

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

Source: Stack Overflow

Solution Source

Related Questions