CODEWITHSUNDEEP
mysqljoin
fredley
fredley

Reputation: 33891

Problems with MySQL COUNT() and joins

I need to get a count of which types of tickets have been ordered on each account, and only on accounts that have incurred a transaction. I'm doing this with the following query for each price:

SELECT tickets.order_id as order_id, count(tickets.id) as count 
  FROM tickets,transactions 
  WHERE price = $price 
    AND tickets.order_id = transactions.order_id 
  GROUP BY tickets.order_id

This gets the right set of results, but on orders where there are multiple transactions the number for count(tickets.id) is multiplied by that number. What query do I need to use to avoid this problem? Do I need to use a different kind of join?

Upvotes: 0

Views: 557

Answers (1)

Pentium10
Pentium10

Reputation: 207838

use

count (distinct tickets.id)

Upvotes: 6

Related Questions