How to Efficiently Aggregate Multiple Columns from Different Tables in a Single MySQL Statement?

Consolidating Data from Multiple Tables with a Single MySQL Query

Initially, using multiple subqueries to aggregate data from various tables yielded inaccurate results, with only the first column showing correct values. To resolve this, a more efficient method is required.

The most effective solution involves conditional aggregation within a single table query. This approach avoids the limitations of multiple subqueries and produces accurate results. The following query demonstrates this technique:

<code class="language-sql">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</code>

This query uses the IF() function to conditionally count occurrences based on the PAT_STATUS column. Each row is counted only once, contributing to a single aggregated column. The SUM() function then totals these conditional counts for each DAY_IN.

This single-query approach provides a concise and accurate method for aggregating data from different columns within the same table, thereby resolving the issue of inaccurate results obtained from the original multiple subquery method.

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