Как включить информацию из двух запросов в одну итоговую таблицу?

Question

У меня есть следующие запросы:

select year, avg(value) as winter_avg_Miami from AP_DATA where substring(series_id, 8) ='killowatts' and  substring(series_id, 3,5) = 'Miami' and period IN ('M10','M11','M12') group by year;
select year, avg(value) as winter_avg_notMiami from AP_DATA where substring(series_id, 8) = 'killowatts' and  substring(series_id, 3,5) != 'Miami' and period IN ('M10','M11','M12') group by year;

Я хочу вместо двух разных таблиц, например:

year       winter_avg_Miami
2000             28.1
2001             30.2

и еще один стол, как:

year        winter_avg_notMiami
2000             40.1
2001             50.2

Я хочу просто иметь всю информацию в одной таблице, например:

year       winter_avg_Miami  winter_avg_notMiami
2000             28.1              40.1 
2001             30.2              50.2

Как я могу написать свой запрос, чтобы выполнить это?

Answer 1

Вы можете использовать case утверждение в вашем avg агрегатный:

select year, 
   avg(case when substring(series_id, 3,5) = 'Miami' then value end) as winter_avg_Miami,
   avg(case when substring(series_id, 3,5) != 'Miami' then value end) as winter_avg_notMiami 
from AP_DATA 
where substring(series_id, 8) ='killowatts'
    and period IN ('M10','M11','M12') 
group by year

• Демонстрация сжатой SQL скрипки

Answer 2

select
      year,
      case when CntMiami = 0 then 0 else TotMiami / CntMiami end ) as AvgMiami,
      case when CntNotMiami = 0 then 0 else TotNotMiami / CntNotMiamt end ) as AvgNotMiami
from
   (
select 
      year, 
      sum( case when substring(series_id, 3,5) = 'Miami' then value else 0 end ) as TotMiami,
      sum( case when substring(series_id, 3,5) = 'Miami' then 1 else 0 end ) as CntMiami,
      sum( case when substring(series_id, 3,5) != 'Miami' then value else 0 end ) as TotNotMiami,
      sum( case when substring(series_id, 3,5) != 'Miami' then 1 else 0 end ) as CntNotMiami
   from 
      AP_DATA 
   where 
          substring(series_id, 8) ='killowatts' 
      and period IN ('M10','M11','M12') 
   group by 
      year )

Answer 3

Если ваши запросы верны, это должно работать:

SELECT a.year, a.winter_avg_Miami, b.winter_avg_notMiami
From (select year, avg(value) as winter_avg_Miami 
     from AP_DATA where substring(series_id, 8) ='killowatts' and  
         substring(series_id, 3,5) = 'Miami' and period IN ('M10','M11','M12') 
     group by year)a, 
    (select year, avg(value) as winter_avg_notMiami 
    from AP_DATA where substring(series_id, 8) = 'killowatts' and  
    substring(series_id, 3,5) != 'Miami' and period IN ('M10','M11','M12') 
    group by year) b
WHERE a.year = b.year;

Answer 4

СОЮЗЫ - простая альтернатива, если есть какая-то причина, по которой вы не можете сделать один из более традиционных способов, таких как Шеддес.

Answer 5

Пожалуйста, попробуйте ниже Решения:

решение: 1

select ap1.year, avg(ap1.value) as winter_avg_Miami,avg(ap2.value) as winter_avg_notMiami
from AP_DATA ap1 left join ap2.AP_DATA on ap1.year=ap2.year
where substring(ap1.series_id, 8) ='killowatts' and  substring(ap1.series_id, 3,5) = 'Miami' and ap1.period IN ('M10','M11','M12') 
substring(ap2.series_id, 8) = 'killowatts' and  substring(ap2.series_id, 3,5) != 'Miami'and ap2.period IN ('M10','M11','M12')
group by ap1.year;

Решение: 2

--create temp table
create table temp1
(year int, winter_avg_Miami int null,winter_avg_notMiami int null)

--insert into temp table
insert into temp1
(year int, winter_avg_Miami ,winter_avg_notMiami)
(
select year, avg(value) as winter_avg_Miami,null as winter_avg_notMiami from AP_DATA where substring(series_id, 8) ='killowatts' and  substring(series_id, 3,5) = 'Miami' and period IN ('M10','M11','M12') group by year
union all
select year, null as winter_avg_Miami, avg(value) as winter_avg_notMiami from AP_DATA where substring(series_id, 8) = 'killowatts' and  substring(series_id, 3,5) != 'Miami' and period IN ('M10','M11','M12') group by year
)

--result in single table
select year,avg(winter_avg_Miami) as winter_avg_Miami,avg(winter_avg_notMiami) as winter_avg_notMiami
from temp1
group by year

Я пробовал выше решения в Sybase. это работало нормально.

Надеюсь это поможет:)