How to Efficiently Aggregate Data from Multiple Columns Across Different Tables in MySQL?

Efficiently Summarizing Data from Multiple Columns in MySQL

Working with relational databases often requires aggregating data spread across multiple columns in different tables. Simply concatenating multiple queries can lead to inaccurate results. This example demonstrates a common problem and its efficient solution using MySQL.

Consider this initial MySQL query designed to summarize patient data from the t_hospital table:

SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;

SELECT* from
   ( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )e,
     (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS  like '%ong%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN ) a,
        (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS  like '%rtde%'and  DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )b,
           (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS  like '%pol%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN   )c,
              (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS  like '%para%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )d;

The problem: Only arr (counting DAY_IN) produces correct results. ONG1, RTED, POLI, and para (counting occurrences of different PAT_STATUS values) are inaccurate due to the improper joining of separate queries.

The Solution: Employing MySQL's aggregate functions (SUM and IF) within a single query provides a much more efficient and accurate solution:

SELECT DAY_IN, COUNT(*) AS arr,
        SUM(IF(PAT_STATUS  like '%ong%', 1, 0)) AS ONG1,
        SUM(IF(PAT_STATUS  like '%rtde%', 1, 0)) AS RTED,
        SUM(IF(PAT_STATUS  like '%pol%', 1, 0)) AS POL1,
        SUM(IF(PAT_STATUS  like '%para%', 1, 0)) AS para
FROM t_hospital 
WHERE DAY_IN between @start_check and @finish_check 
    and RES_DATE between @start_res and @finish_res  
    and ID_daily_hos =@daily_hos
GROUP BY DAY_IN;

This revised query correctly aggregates the data for each DAY_IN, providing accurate counts for each PAT_STATUS category within a single, efficient query. This approach avoids the Cartesian product and resulting inaccuracies of the original query.

The above is the detailed content of How to Efficiently Aggregate Data from Multiple Columns Across Different Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!