Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Aggregate Incorrect with INNER JOIN

    I have the following two tables

    tbl_tickets

    id - INT Primary Key
    ticket_id VARCHAR(80)
    start_time DATETIME
    end_time DATETIME
    category VARCHAR(80)


    tbl_ticket_category_type

    ticket_id VARCHAR(80) Primary Key also references tbl_tickets on ticket_id
    category_type VARCHAR(80)

    I want to work out average transaction time on tbl_tickets where category_type != 'No Show'

    I've tried the following but it doubles the records before working out the average:

    Code:
    SELECT avg(datediff(s, start_time, end_time)) 
    FROM tbl_tickets t1
    INNER JOIN tbl_ticket_category_type t2 ON t2.ticket_id = t1.ticket_id
    WHERE t2.category_type != 'No Show'
    Any one know why that is?

    to illustrate what i mean is instead of doing an avg if you do a count as follows you will note the count is doubled simply by doing an inner join:

    Code:
    SELECT count(t1.id) 
    FROM tbl_tickets t1
    INNER JOIN tbl_ticket_category_type t2 ON t2.ticket_id = t1.ticket_id
    WHERE t2.category_type != 'No Show'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dump your rows of data, the answer is duplicated rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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