MySQL failing to process Join Query with group by clause-CodePudding

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.

CodePudding user response：

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