Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    30

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

    Hello,


    I am using the latest MySQL and I am doing a union all given me two line 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 results are correct, but I would like to the second line to be within the first line (transfer is correct at 74 I want that to show on the first line) 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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try this...
    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_interflowed)
                + SUM(dn_abndn2)    + SUM(dn_abndn3)    + SUM(dn_abndn4)    + SUM(dn_abndn5)    + SUM(dn_abndn6)
              , 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"
         , if(  dnis_id IN (2, 23, 32)
              , SUM(dn_transferred)
              , 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, 2, 23, 32)
     group by
           dn_date

  3. #3
    Join Date
    Mar 2013
    Posts
    30
    Thanks Tonkuma, I appreciate your help. Yes I was able to get one row, but Total Calls, Answ, and ABND were 0, which should be an amount. The transfer is 129 instead of 74 its picking up other transfer from different department. Just need to fine tune it. If you can suggest, please let me know. Thanks for your help.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry,
    my mistake.


    If all columns were NOT NULLable,
    please try this...
    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
         , SUM( if(   dnis_id IN (31, 34, 35, 36, 39)
                   ,  dn_answered1 + dn_answered2 + dn_answered3 + dn_answered4 + dn_answered5 + dn_answered6
                    + dn_interflowed
                    + dn_abndn2    + dn_abndn3    + dn_abndn4    + dn_abndn5    + dn_abndn6
                   , 0
                  )
              ) as Total_Calls
         , SUM( if(   dnis_id IN (31, 34, 35, 36, 39)
                   ,  dn_answered1 + dn_answered2 + dn_answered3 + dn_answered4 + dn_answered5 + dn_answered6
                    + dn_interflowed
                   , 0
                  )
              ) As " Answ"
         , SUM( if(   dnis_id IN (31, 34, 35, 36, 39)
                   ,  dn_abndn2    + dn_abndn3    + dn_abndn4    + dn_abndn5    + dn_abndn6
                   , 0
                  )
              ) As "ABND"
         , SUM( if(   dnis_id IN (2, 23, 32)
                   ,  dn_transferred
                   , 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, 2, 23, 32)
     group by
           dn_date


    If some columns were NULLable,
    please try...
    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
         ,   SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered1 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered2 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered3 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered4 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered5 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_answered6 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_interflowed , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_abndn2 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_abndn3 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_abndn4 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_abndn5 , 0) )
           + SUM( if(dnis_id IN (31, 34, 35, 36, 39) , dn_abndn6 , 0) )
           as Total_Calls
    ...
    ...
     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, 2, 23, 32)
     group by
           dn_date
    or

    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
         ,   SUM( if(  dnis_id IN (31, 34, 35, 36, 39)
                     ,  COALESCE(dn_answered1 , 0)
                      + COALESCE(dn_answered2 , 0)
                      + COALESCE(dn_answered3 , 0)
                      + COALESCE(dn_answered4 , 0)
                      + COALESCE(dn_answered5 , 0)
                      + COALESCE(dn_answered6 , 0)
                      + COALESCE(dn_interflowed , 0)
                      + COALESCE(dn_abndn2 , 0)
                      + COALESCE(dn_abndn3 , 0)
                      + COALESCE(dn_abndn4 , 0)
                      + COALESCE(dn_abndn5 , 0)
                      + COALESCE(dn_abndn6 , 0)
                     , 0
                    )
                ) as Total_Calls
    ...
    ...
     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, 2, 23, 32)
     group by
           dn_date

  5. #5
    Join Date
    Mar 2013
    Posts
    30

    Thumbs up Thank you

    Tonkuma, I am just learning MySQL and you are great friend in helping me. I used your code and it works. Again I want to say thanks for all your help and time. You saved me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •