INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Create one row results from union all instead of two rows

Create one row results from union all instead of two rows

Create one row results from union all instead of two rows

(OP)
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

RE: Create one row results from union all instead of two rows

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close