Create one row results from union all instead of two rows

[donalexander]

Hello,


I am using the latest MySQL and I am doing a union all given me two rows result, but what I really want is to have my results in one line instead of two. Here are the following fields:
Day of Week, Date, Month Number, Month Name, Day of the week, year, Total Calls, Answ, ABND, and Transfer. The Total Calls, Answ, ABND from the second Select statement should be zero, The results are correct, but I would like to see the second row field(transfer), which is 74 to be in the first row (transfer) How can I do this. Any tips will be appreciated. I try to stay away of union all and just have everything in one line, but the results were incorrect. Thank you.


Results
3 2013-09-03 9 September Tuesday 2013 827 790 37 0
3 2013-09-03 9 September Tuesday 2013 0 0 0 74


Here is my code
SELECT DAYOFWEEK(dn_date) AS DayOfWeek, dn_date, MONTH(dn_date) as "Month Number", convert(MONTHname(dn_date) using latin1) as Month, convert(DAYNAME(dn_date) using latin1) as Day, Year(dn_date) as Year, if(dnis_id IN(31, 34, 35, 36, 39), (SUM(dn_answered1) + SUM(dn_answered2) + SUM(dn_answered3) + SUM(dn_answered4) + SUM(dn_answered5) + SUM(dn_answered6) + SUM(dn_abndn2) + SUM(dn_abndn3) + SUM(dn_abndn4) + SUM(dn_abndn5) + SUM(dn_abndn6) + SUM(dn_interflowed)),0) as Total_Calls, if(dnis_id IN(31, 34, 35, 36, 39), (SUM(dn_answered1) + SUM(dn_answered2) + SUM(dn_answered3) + SUM(dn_answered4) + SUM(dn_answered5) + SUM(dn_answered6) + SUM(dn_interflowed)),0) As " Answ", if( dnis_id IN(31, 34, 35, 36, 39), SUM(dn_abndn2) + SUM(dn_abndn3) + SUM(dn_abndn4) + SUM(dn_abndn5) + SUM(dn_abndn6),0) As "ABND" , SUM(0+0) as Transfer FROM ecc.dno where dn_date = "2013-9-03" and DAYOFWEEK(dn_date) between 2 and 6 and dn_time between 360 and 1019 and dnis_id IN(31, 34, 35, 36, 39) group by dn_date
union all
SELECT DAYOFWEEK(dn_date) AS DayOfWeek, dn_date, MONTH(dn_date) as "Month Number", convert(MONTHname(dn_date) using latin1) as Month, convert(DAYNAME(dn_date) using latin1) as Day, Year(dn_date) as Year, 0 as Total_Calls, 0 As " Answ", 0 As "ABND" , SUM(dn_transferred) as Transfer FROM ecc.dno where dn_date = "2013-9-03" and DAYOFWEEK(dn_date) between 2 and 6 and dn_time between 360 and 1019 and dnis_id IN(2, 23, 32) group by DayOfWeek

 

[ChrisHirst]

That is what a UNION does, it joins row sets from seperate queries on the same data structures into a single set of rows.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum