Firebird 1.5 Union

Question

I'm trying to get a report to show different results in each line

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)

the above, for example, will give me 4 columns with 1 line result. Now I would like to have March (for example) on the second line ... and so on.

suggestions?

Your query is unreadable wall of text. Can you format it and also provide your DB schema as well as sample data and desired result? Otherwise I doubt someone will waste his time in attempts to understand your question — Andy Korneyev, May 23 '17 at 10:20
Apologies. Thought it could be seen correctly. Count on Inbound and outbound calls on 2 different clients. on each month. I have used 'union all' and works. but I'd like to have a different name on each line... — Leo Fazzi, May 23 '17 at 10:48
I'm guessing you're looking to use a Union since you included it in your title? If so, all you need to do is basically put the `Union` keyword after the first `from`, copy the `select` and the `from` and paste it, adjust the date logic part to what you want, and make the column aliases all the same name. You'll need to do this for however many months you wish to select. The `order by` will be your last clause for the entire statement. It'll be a bit sloppy, and there are likely other ways to achieve what you want, but without knowing the how the DB is setup, it's difficult to tell. — Timothy G., May 23 '17 at 11:04
select count bla bla bla from calls... union all and then select count bla bla bla from calls. I am using the same coulmn alias but I would like a unique name for each line... — Leo Fazzi, May 23 '17 at 11:29

René Hoffmann · Answer 1 · 2017-05-23T12:37:24.563

0

Imho, you are better off using a GROUP BY for this:

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)

Note, how I included EXTRACT() evaluation after select, where and group by.

edited May 23 '17 at 12:37

answered May 23 '17 at 11:42

René Hoffmann

2,647
2
18
37

Hey Hi Rene! extract(month from c.call_start) as Month gives me an error: Dynamic sql error sql error code= -104 token unknown - line2, char 37 Month – Leo Fazzi May 23 '17 at 11:55
I am sorry! `month` is a reserved identifier in Firebird. You have to give the column another name or put it in quotes. I corrected my answer accordingly. – René Hoffmann May 23 '17 at 12:38
Interesting!!! so if I want October 2016 to May 2017 ? can't do month `10 to 5 but can add an or extract bla bla .. works but the result on each row is 10 5 11 12 2 1 – Leo Fazzi May 23 '17 at 12:47
The result for **what** column is "10 5 11 12 2 1"? Or is this the result you get for all columns of every row? – René Hoffmann May 23 '17 at 12:50
10,11,12,1,2,3,4,5 would be the desired order.. even better if I could call 10 October, 11 November .. but not really bothered about that – Leo Fazzi May 23 '17 at 12:51
For this to happen, you could add another column `extract(year from c.call_start) as myYear`. Then you would `group by` and `order by` these both columns. This would result in the desired order. – René Hoffmann May 23 '17 at 12:53
To output the month names, you could surround the extract-month-statement with a case-statement which returns the names for cases 1 to 12. – René Hoffmann May 23 '17 at 12:55

Firebird 1.5 Union

1 Answers1