Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: Merge two count columns with different number of results

    Hello,

    I'm struggling with merging two queries which presents counts calculated from the same table but presenting different number of results.

    1st Query:

    select tkstatus.ASSIGNEDOWNERGROUP, count (*) as NumT
    from tkstatus
    left join ticket on tkstatus.ticketid = ticket.TICKETID
    where tkstatus.class='INCIDENT'
    and tkstatus.status = 'CLOSED'
    and ticket.internalpriority = 1
    and ticket.ISGLOBAL = 0
    group by ticket.internalpriority, tkstatus.ASSIGNEDOWNERGROUP

    Presents 46 results:

    ASSIGNEDOWNERGROUP NUMT
    ---------------------------- -------
    3S SERVICE OPERATIONS GLOBAL 6
    3S TEAM DATA CENTER 27
    AUTH-CROSS-TOPIC-SUPPORT 1
    BM-SUPPORT 6
    BUSINESS-CONNECTOR-SUPP 1
    CLIENT-OPERATION 96
    CLIENT-SECURITY 1
    CPSS-GLOBAL-SUPPORT 1
    CPSS-SUPPORT 1
    CRM-MARKETING-MAN-SUPPORT 1
    CSOLP 5
    DATABASE-ADMIN 2
    EDATA-SUPPORT 2

    2nd query:

    select tkstatus.ASSIGNEDOWNERGROUP, count(*) as Numbertickets2
    from tkstatus left join ticket on tkstatus.ticketid = ticket.ticketid
    where ticket.actualfinish < ticket.targetfinish
    and tkstatus.class='INCIDENT'
    and tkstatus.status = 'CLOSED'
    and ticket.internalpriority = 1
    and ticket.ISGLOBAL = 0
    group by ticket.internalpriority,tkstatus.ASSIGNEDOWNERGROU P

    Presents 23 results:

    ASSIGNEDOWNERGROUP NUMBERTICKETS2
    ---------------------------- -----------------
    3S SERVICE OPERATIONS GLOBAL 3
    3S TEAM DATA CENTER 5
    CLIENT-OPERATION 12
    CSOLP 1
    FIREWALL-OPERATION 9
    GD-PICTURE-CARD 6
    GD_INC_COORD_GDM 11
    GLOBALONE-SUPPORT 1
    HP-QC-SUPPORT 1
    HR-SUPPORT 1
    ITSD 25
    LOCAL-2ND-IT@GDMAL 1
    LOCAL-2ND-IT@LOMO 1

    I want merge it all to one table where will be all columns I mean :

    ASSIGNEDOWNERGROUP NUMT NUMBERTICKETS2

    all nulls should be replaced by 0

    I would appreciate for your help.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Dan,

    Try this:
    Code:
    select 
      coalesce(g1.ASSIGNEDOWNERGROUP, g2.ASSIGNEDOWNERGROUP) ASSIGNEDOWNERGROUP
    , coalesce(g1.NUMT, 0) NUMT
    , coalesce(g2.NUMBERTICKETS2, 0)  NUMBERTICKETS2 
    from (
    select tkstatus.ASSIGNEDOWNERGROUP, count (*) as NumT
    from tkstatus
    left join ticket on tkstatus.ticketid = ticket.TICKETID
    where tkstatus.class='INCIDENT'
    and tkstatus.status = 'CLOSED'
    and ticket.internalpriority = 1
    and ticket.ISGLOBAL = 0
    group by ticket.internalpriority, tkstatus.ASSIGNEDOWNERGROUP
    ) g1
    full join (
    select tkstatus.ASSIGNEDOWNERGROUP, count(*) as Numbertickets2
    from tkstatus left join ticket on tkstatus.ticketid = ticket.ticketid
    where ticket.actualfinish < ticket.targetfinish
    and tkstatus.class='INCIDENT'
    and tkstatus.status = 'CLOSED'
    and ticket.internalpriority = 1
    and ticket.ISGLOBAL = 0
    group by ticket.internalpriority,tkstatus.ASSIGNEDOWNERGROU P
    ) g2 on g1.ASSIGNEDOWNERGROUP=g2.ASSIGNEDOWNERGROUP
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2013
    Posts
    2

    Thanks it works very well! :)

    Thanks it works very well!

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by DanPetrescu View Post
    Thanks it works very well!
    Here's another alternative

    Code:
    select tkstatus.ASSIGNEDOWNERGROUP
            , count (*) as NumT
            , count( case when ticket.actualfinish < ticket.targetfinish then 1 end ) as Numbertickets2
    from tkstatus
    left join ticket 
        on tkstatus.ticketid = ticket.TICKETID
    where tkstatus.class='INCIDENT'
      and tkstatus.status = 'CLOSED'
      and ticket.internalpriority = 1
      and ticket.ISGLOBAL = 0
    group by ticket.internalpriority
                 , tkstatus.ASSIGNEDOWNERGROUP
    --
    Lennart

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Your schema is a mess. A status is an attribute; attributes are columns and never tables!! Do you really have only one ticket?? Read ISO-11179 or any book on data modeling; this what that table name tells us.

    Even worse, it looks like “is_global” is bit flag! We do not use flags in SQL. Never. This a fundamental concept.

    The problem is that you have no idea how to create a valid, usable data model or schema. Post your DDL so we can re-write this. You have garbage. Your approach is completely wrong.

    Here is guess at a skeleton:

    SELECT assigned_owner_group, COUNT (*) AS group_ticket_cnt
    FROM Tickets
    WHERE ticket_class = 'INCIDENT'*
    AND ticket_status = 'CLOSED'
    AND internal_priority = 1
    GROUP BY assigned_owner_group;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by DanPetrescu View Post
    Hello,

    I'm struggling with merging two queries which presents counts calculated from the same table but presenting different number of results.

    1st Query:

    select tkstatus.ASSIGNEDOWNERGROUP, count (*) as NumT
    from tkstatus
    left join ticket on tkstatus.ticketid = ticket.TICKETID
    where tkstatus.class='INCIDENT'
    and tkstatus.status = 'CLOSED'
    and ticket.internalpriority = 1
    and ticket.ISGLOBAL = 0
    group by ticket.internalpriority, tkstatus.ASSIGNEDOWNERGROUP

    ...
    ...
    (1) If you specified a condition for inner table(i.e. ticket) in WHERE clause,
    the result of the OUTER JOIN got same as the result of an INNER JOIN.

    (2) You specified "and ticket.internalpriority = 1" in WHERE clause, and the column was not in select list.
    So, ticket.internalpriority need not to be included in grouping columns.


    As a conseuence,
    please try the examples.

    Example 1: Include ASSIGNEDOWNERGROUP without ticket(i.e. numt = 0) in the result.
    Code:
    SELECT ts.ASSIGNEDOWNERGROUP
         , COUNT(t.ticketid) AS numt
         , COUNT(
              CASE
              WHEN t.actualfinish < t.targetfinish THEN
                   0
              END
           ) AS numbertickets2
     FROM  tkstatus AS ts
     LEFT  OUTER JOIN
           ticket   AS t
      ON   t.ticketid = ts.ticketid
       AND t.internalpriority = 1
       AND t.ISGLOBAL         = 0
     WHERE ts.class  = 'INCIDENT'
       AND ts.status = 'CLOSED'
     GROUP BY
           ts.ASSIGNEDOWNERGROUP
    ;

    Example 2: Do not include ASSIGNEDOWNERGROUP without ticket in the result.
    (i.e. all rows in the result are "numt > 0")
    Note: You can move some(or all) of conditions in WHERE clause into ON condition.
    Code:
    SELECT ts.ASSIGNEDOWNERGROUP
         , COUNT(*) AS numt
         , COUNT(
              CASE
              WHEN t.actualfinish < t.targetfinish THEN
                   0
              END
           ) AS numbertickets2
     FROM  tkstatus AS ts
     INNER JOIN
           ticket   AS t
      ON   t.ticketid = ts.ticketid
     WHERE ts.class  = 'INCIDENT'
       AND ts.status = 'CLOSED'
       AND t.internalpriority = 1
       AND t.ISGLOBAL         = 0
     GROUP BY
           ts.ASSIGNEDOWNERGROUP
    ;

Posting Permissions

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