Firebird 1.5 Union

Question

Я пытаюсь получить отчет, чтобы показать разные результаты в каждой строке

select
count(case when call_type='I' and cl.client_ID not like 391 and c.call_start  >= '2017/04/01' and c.call_start  <= '2017/04/30'then 1 else null end) as Incoming_Main_April, 
count(case when call_type='O' and cl.client_ID not like 391 and c.call_start  >= '2017/04/01' and c.call_start  <= '2017/04/30'then 1 else null end) as Outgoing_Main_April,
count(case when call_type='I' and cl.client_ID=391 and c.call_start  >= '2017/04/01' and c.call_start  <= '2017/04/30'then 1 else null end) as Incoming_SMG_April,
count(case when call_type='O' and cl.client_ID=391 and c.call_start  >= '2017/04/01' and c.call_start  <= '2017/04/30'then 1 else null end) as Outgoing_SMG_April

from
  CALLS c 
  left outer join CONTACTS ct on c.CONTACT_ID= ct.CONTACT_ID
  left outer join clients cl on cl.client_id= ct.COMPANY_ID where cl.RECORD_STATUS='A' 
  and c.OPERATOR_ID in (1510,2938,12443,4482,8911,6947,2056,1969,1952,2223,1511,2224,2039,2055,2085,1949,5963,1502,11112,1633,2034,2057)  

order by
  count(call_type)

выше, например, даст мне 4 столбца с 1 строкой результата. Теперь я хотел бы, чтобы март (например) был на второй строчке... и так далее.

предложения?

Answer 1

Имхо, вам лучше использовать GROUP BY за это:

select
  extract(month from c.call_start) as MyMonth,
  count(case when call_type='I' and cl.client_ID not like 391 then 1 else null end) as Incoming_Main, 
  count(case when call_type='O' and cl.client_ID not like 391 then 1 else null end) as Outgoing_Main,
  count(case when call_type='I' and cl.client_ID = 391        then 1 else null end) as Incoming_SMG,
  count(case when call_type='O' and cl.client_ID = 391        then 1 else null end) as Outgoing_SMG
from
  CALLS c 
  left outer join CONTACTS ct on c.CONTACT_ID= ct.CONTACT_ID
  left outer join clients cl on cl.client_id= ct.COMPANY_ID
where cl.RECORD_STATUS='A' 
  and c.OPERATOR_ID in (1510,2938,12443,4482,8911,6947,2056,1969,1952,2223,1511,2224,2039,2055,2085,1949,5963,1502,11112,1633,2034,2057)
  and extract(month from c.call_start) between 3 and 4 -- only return entries from March and April
group by
  extract(month from c.call_start)
order by
  count(call_type)

Обратите внимание, как я включил EXTRACT() оценка после select, where а также group by,