Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Problem with HAVING/COUNT

    I have a table (history) with ticket_id, tech_id, statusdate

    ticket_id will show up multiple times.
    And tech_id can be multiple times in ticket_id

    Example

    ticket_id, tech_id
    100 1
    100 1
    100 2
    101 2
    102 1
    102 1



    I am trying to get a count of ticket_id that have more than 1 tech_id

    So,

    SELECT COUNT(DISTINCT ticket_id)
    FROM history
    where
    statusdate between '2013-03-01' and '2013-05-30'

    HAVING COUNT( tech_id) > 1

    But I do not think this is correct.

    Any help?
    thanks

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Look up GROUP BY.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT COUNT(DISTINCT a.ticket_id) 
       FROM history AS a
       WHERE  a.statusdate BETWEEN '2013-03-01' AND '2013-05-30'
          AND 1 < (SELECT Count(DISTINCT z.tech_id)
             FROM history AS z
             WHERE  z.ticket_id = a.ticket_id)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Pat,

    I tried your solution and it returns 1!

    I then tried my own suggestion and came up with 2.

    Of course I had to add a statusdate since the OP did not provide one.

    This is what I used for data:

    Code:
    declare @t1 table
    (
         ticket_id int
       , tech_id int
       , statusdate date
     )
     
     insert @t1 (ticket_id, tech_id, statusdate) 
        values
     (100, 1, '2013-05-01')
    ,(100, 1, '2013-05-01')
    ,(100, 2, '2013-05-13')
    ,(101, 2, '2013-01-01')
    ,(101, 2, '2013-05-01')
    ,(102, 1, '2013-03-01')
    ,(102, 1, '2013-04-01')
    and then this query:

    Code:
    select COUNT(*) as TicketID_Cnt
     from 
       (   select COUNT(*) as cnt
            from @t1
           where statusdate between '2013-03-01' and '2013-05-30'
           group by ticket_id
       ) as cte
    where cte.cnt > 1
    Am I interpreting the question incorrectly?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the criteria:
    Quote Originally Posted by gregd101 View Post
    I am trying to get a count of ticket_id that have more than 1 tech_id
    Based on the original data, the ticket and the number of different tech_id values are:
    Code:
                     Unique
    Ticket  Rows   Tech_ids
    100        3          2
    101        1          1
    102        2          1
    Your code uses different base data, and a different kind of computation. Run your sub-query by itself and see if that is the result that you expect. I think that you'll be surprised!

    -PatP
    Last edited by Pat Phelan; 05-31-13 at 00:12.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Ahh, unique tech_ids.

    This minor change then matches the hopefully desired output as well as your query results.

    Code:
    select COUNT(*) as TicketID_Cnt
     from 
       (   select COUNT(DISTINCT tech_id) as cnt
            from history
           where statusdate between '2013-03-01' and '2013-05-30'
           group by ticket_id
       ) as cte
    where cte.cnt > 1

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be surprised if your code returned anything but 1, but I don't have a MySQL system here to test your code. Using SQL Server 2012 (what I have available here), it does return 1.

    My "z" alias counts the number of tech_ids per ticket, and based on the original requirement that is not constrained by date. As long as the ticket had any work done in the appropriate date range, every tech who ever worked that ticket is eligible for this count.

    My "a" alias finds the tickets that meet the date range criteria and uses the correlated "z" alias to find if those tickets have multiple techs.

    I don't know of a simpler way to solve this problem due to having to count two different things using different criteria.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by gregd101 View Post
    I have a table (history) with ticket_id, tech_id, statusdate

    Example

    ticket_id, tech_id
    100 1
    100 1
    100 2
    101 2
    102 1
    102 1
    I think the original data is incomplete based on the description of the table. The statusdate should be in the table. Where else would the date come from?

    I added additional records to the test data, inside and outside of the date range, some with duplicate tech_ids, some unique and both of our queries came up with the same number.

    A bigger data set should expose any problems, but with what was given, they both seem to work.

    Maybe gregd101 will step in and provide some clarification.

Posting Permissions

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