Is it necessary to nest a double COUNT here?
<p>So, I have three tables for a service that sells tickets for events:</p>

<pre><code>Event(id)
Ticket(id, venue_id, seller_id)
Venue(id, event_id)
Seller(id)
</code></pre>

<p>And I would like to detect, for every event that I have, how many sellers (of type 1) there are that have only only ticket for sale (and for that ticket to be of the corresponding event). Like</p>

<pre><code>Event_id; First_time_sellers
2814;3
3092;24
124;1
...
</code></pre>

<p>I have attempted this with two queries, both failing getting wrong results</p>

<pre><code>SELECT event_id,
    COUNT(DISTINCT rookie_seller)
FROM
(SELECT event_id,
    rookie_seller,
    tickets
FROM
(SELECT DISTINCT event.id AS event_id,
    seller.id AS rookie_seller,
    COUNT(DISTINCT ticket.id) AS tickets
FROM ticket
JOIN venue ON ticket.venue_id = venue.id
JOIN event ON venue.event_id = event.id
JOIN seller ON ticket.seller_id = seller.id
WHERE seller.type = 1
AND ticket.isforsale = 1
GROUP BY rookie_seller) a
WHERE tickets = 1) b
GROUP BY event_id
</code></pre>

<p>and </p>

<pre><code>SELECT event.id,
    a.seller_id,
    a.tickets
FROM ticket
JOIN (
    SELECT seller.id AS seller_id,
    COUNT(DISTINCT ticket.id) AS tickets
FROM ticket
JOIN seller ON ticket.seller_id = seller.id
WHERE seller.type = 1
AND ticket.isforsale = 1
GROUP BY seller_id) a ON ticket.seller_id = a.seller_id
JOIN venue ON ticket.venue_id = venue.id
JOIN event ON venue.event_id = event.id
WHERE a.tickets = 1
</code></pre>

<p>I am really not sure what the problem is. the joins should be fine, and the conditions on the count as well, right?</p>

<p>Thanks for any piece of advice :)</p>