MySQL failing to process Join Query with group by clause

December 15, 2022

Select deaths.County_Name as County, 
       max(cases.Cumulative_cases) as Cases, 
       max(deaths.Total_Deaths) as Deaths,
           max(deaths.Population2019) as Population2019
From pennsylvaniadeathsbycounty deaths
Join pennsylvaniacasesbycounty cases
    on deaths.County_Name = cases.Jurisdiction
group by County;

I get the following error: Error Code: 2013. Lost connection to MySQL server during query

The Jurisddiction and County_Name columns are identical between the two tables. I want to get a table that displays each counties deaths an cases as well as the the populations of each county. When I run the query it just loads for a while then returns the above error.

>Solution :

The query is timing out most likely, you could try union all instead of joining like this

select County,
       SUM(Cases) Cases,
       SUM(Deaths) Deaths,
       SUM(Population2019) Population2019
from(
Select deaths.County_Name as County, 
       0 as Cases, 
       deaths.Total_Deaths as Deaths,
       deaths.Population2019 as Population2019
From pennsylvaniadeathsbycounty deaths
union all
Select cases.Jurisdiction as County, 
       cases.Cumulative_cases as Cases, 
       0 as Deaths,
       0 as Population2019
From pennsylvaniacasesbycounty cases) a
group by County