How do I combine these tables?

Issue

My goal is to have a 5 by 5 table that looks like this:

I have found the queries separately:

select re.state, max(TotalrenewableE) as Most_RE
from #temp_RE as re
Inner join(
select max(TotalRenewableE) TotalRE
from #temp_RE)MaxTRE
On MaxTRE.TotalRE = RE.TotalRenewableE
Group by re.state 


select nre.state, max(TotalNonrenewableE) as Most_NRE
from #temp_NonRE as nre
Inner join(
select max(totalnonrenewablee) TotalNRE
from #temp_NonRE)MaxTNRE
On MaxTNRE.TotalNRE = NRE.TotalNonrenewableE
Group by nre.state



select re.state, min(TotalrenewableE) as Least_RE
from #temp_RE as re
Inner join(
select min(TotalRenewableE) TotalRE
from #temp_RE)MinTRE
On MinTRE.TotalRE = RE.TotalRenewableE
Group by re.state 



select nre.state, min(TotalNonrenewableE) as Least_NRE
from #temp_NonRE as nre
Inner join(
select min(totalnonrenewablee) TotalNRE
from #temp_NonRE)MinTNRE
On MinTNRE.TotalNRE = NRE.TotalNonrenewableE
Group by nre.state

I have tried:

select nre.state, 
min(TotalNonrenewableE) as Least_NRE,
min(TotalRenewableE) as Least_RE,
max(TotalNonrenewableE) as Most_NRE, 
max(TotalRenewableE) as Most_RE,
from #temp_NonRE as nre, #temp_RE as re
Inner Join(
select min(totalnonrenewablee) TotalNRE
from #temp_NonRE)MinTNRE
On MinTNRE.TotalNRE = #temp_NonRE.TotalNonrenewableE
Inner Join(
select min(TotalRenewableE) TotalRE
from #temp_RE)MinTRE
On MinTRE.TotalRE = RE.TotalRenewableE
Inner Join(
select max(totalnonrenewablee) TotalNRE
from #temp_NonRE)MaxTNRE
On MaxTNRE.TotalNRE = #temp_NonRE.TotalNonrenewableE
Inner Join(
select max(TotalRenewableE) TotalRE
from #temp_RE)MaxTRE
On MaxTRE.TotalRE = RE.TotalRenewableE
Where nre.State = re.State
Group by nre.state

But it gives me this error:

The multi-part identifier "#temp_NonRE.TotalNonrenewableE" could not be bound.

I just cant figure out how to combine them to make the table above.

Thank you for the hard work

Solution

Union all and group once again

select state, max(Most_RE) Most_RE, max(Most_NRE) Most_NRE, max(LeastRE) LeastRE, max(LeastNRE) LeastNRE  
from (
    select re.state, max(TotalrenewableE) as Most_RE, null Most_NRE, null LeastRE, null LeastNRE
    from #temp_RE as re
    Inner join(
    select max(TotalRenewableE) TotalRE
    from #temp_RE)MaxTRE
    On MaxTRE.TotalRE = RE.TotalRenewableE
    Group by re.state 
    
    union all
    
    select nre.state, null, max(TotalNonrenewableE) as Most_NRE, null, null
    from #temp_NonRE as nre
    Inner join(
    select max(totalnonrenewablee) TotalNRE
    from #temp_NonRE)MaxTNRE
    On MaxTNRE.TotalNRE = NRE.TotalNonrenewableE
    Group by nre.state
    
    ...
)
group by state

Answered By – Serg

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0