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'
dBforums
>
Aggregate Incorrect with INNER JOIN
09-02-10, 15:37
Linear Mode
